PostgreSQL Tutorial for Beginners: Projects and Time Entries

PostgreSQL accepts SQL through clients such as psql and application drivers. The connection’s database and role determine the objects and privileges available. psql backslash commands are client instructions, not ordinary SQL sent through every driver. To use another database, establish or switch the connection through the client rather than issuing a MySQL-style USE statement.

A schema groups objects inside a database, and the search_path affects which unqualified object name is resolved. In application code, qualify important tables when ambiguity is possible and configure trusted namespaces deliberately. Two schemas can contain different tables named projects, so a successful query is not proof that it read the intended namespace.

GENERATED BY DEFAULT AS IDENTITY provides server-managed integer allocation while permitting explicit values under its documented rules. The primary key establishes uniqueness and presence. A separate UNIQUE project code would protect a business identity if required. Generated allocation alone should not be treated as a replacement for every domain uniqueness constraint.

The child time-entry table refers to the project primary key through a foreign key. This prevents a non-NULL project reference from pointing to an absent parent. NUMERIC stores exact decimal hours, and CHECK(hours>=0) rejects negative known durations. Because missing hours are permitted in this practice scenario, the check is not paired with NOT NULL; an unknown duration remains a separate completeness state.

An inner join connects entries to their projects and returns one row per matching entry. A left join starting from projects preserves projects without entries. Count the entry primary key after that join, rather than COUNT(*), so the preserved placeholder does not become a fictional time entry. SUM(hours) totals known durations only.

Grouping by project identity and its selected name expresses one row per project. Selecting an ungrouped detail field such as the entry date would not fit that summary shape. PostgreSQL recognises some functional dependencies, but grouping explicitly by selected nonaggregate fields keeps this beginner example clear and portable in meaning. Add ORDER BY rather than relying on grouping to provide display order.

INSERT ... RETURNING can return the generated key from the same insertion statement. UPDATE and DELETE also support RETURNING for appropriate workflows. This avoids a separate largest-id lookup that could race with another writer. Bind project names and hour values as parameters; RETURNING does not make concatenated user input safe.

BEGIN, COMMIT and ROLLBACK group row changes. PostgreSQL can also roll back many schema operations, but CREATE DATABASE and DROP DATABASE are notable operations that cannot run inside a transaction block. Choose transaction boundaries according to the actual unit of work. A successful first insert should not survive alone if a required related insert fails and the workflow promised atomic completion.

PostgreSQL supports standard-style CAST and its additional double-colon cast notation. It offers ILIKE for case-insensitive pattern matching and DISTINCT ON for particular row-selection patterns. Those extensions are useful when the application commits to PostgreSQL, but should be labelled when a tutorial also discusses MySQL or SQLite. DISTINCT ON still needs a clear ordering rule to choose a useful representative.

For temporal data, distinguish timestamp without time zone from timestamp with time zone. A project work date is a calendar date, while an event occurrence can require an instant. Use half-open period boundaries for timestamp reporting and define the reporting timezone. Display formatting and timezone interpretation are separate decisions.

EXPLAIN describes a query plan, while EXPLAIN ANALYZE executes the query and adds observed execution measurements. That difference matters for statements with side effects. Statistics, indexes and data distribution influence plan choice; a foreign-key relationship does not automatically guarantee every useful child lookup index is present.

Start with correctness checks on the small fixture: project counts, known hours and missing duration records. Then measure on realistic data. A faster query that loses empty projects or replaces unknown duration with a misleading total is not an equivalent optimisation. Keep result-shape and completeness tests alongside performance investigation.

The setup has two projects. Atlas has two entries, one known and one unknown; Beacon has none. Run it once in a disposable PostgreSQL schema. The deliberately missing duration exposes the difference between counting entries and summing known hours.

CREATE TABLE pg_projects (
 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 name VARCHAR(40) NOT NULL
);
CREATE TABLE pg_entries (
 id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 project_id INTEGER NOT NULL REFERENCES pg_projects(id),
 hours NUMERIC(8,2) CHECK(hours>=0)
);
INSERT INTO pg_projects (id,name) VALUES (1,'Atlas'),(2,'Beacon');
INSERT INTO pg_entries (id,project_id,hours) VALUES (1,1,2.5),(2,1,NULL);

Find real time-entry records with unknown hours and attach the project name. Do not include a project with no entry as an incomplete record.

SELECT p.name,e.id AS entry_id FROM pg_projects AS p
JOIN pg_entries AS e ON e.project_id=p.id
WHERE e.hours IS NULL ORDER BY e.id;

name | entry_id
Atlas | 2

The inner join supplies only real time-entry relationships. The IS NULL filter then identifies entry 2’s missing duration. Beacon is absent because it has no child record to fix. This is a data-quality list rather than a project coverage summary. If hours must become required in production, resolve unknown entries before adding NOT NULL and ensure future writers meet the rule.

Use the PostgreSQL tutorial, identity-column reference and EXPLAIN guide for the corresponding language and execution rules.