Tables, queries and indexes
Run the example in a scratch database. The identity column supplies a dispatch ID. The status constraint limits allowed text, while NOT NULL independently rejects a missing status. timestamptz represents an instant; its displayed time depends on the session time zone.
CREATE TABLE dispatches (
dispatch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch text NOT NULL,
status text NOT NULL CHECK (status IN ('open', 'closed')),
scheduled_at timestamptz,
details jsonb NOT NULL DEFAULT '{}'::jsonb
);
SELECT branch, COUNT(*) AS open_jobs
FROM dispatches
WHERE status = 'open'
GROUP BY branch
HAVING COUNT(*) > 1
ORDER BY open_jobs DESC, branch;
CREATE INDEX dispatch_open_branch
ON dispatches (branch) WHERE status = 'open';
CREATE INDEX dispatch_priority
ON dispatches ((details ->> 'priority'));
CREATE INDEX dispatch_details_gin
ON dispatches USING gin (details);
SELECT dispatch_id FROM dispatches
WHERE details @> '{"priority":"high"}'::jsonb;
BEGIN;
SAVEPOINT before_change;
UPDATE dispatches SET status = 'closed' WHERE dispatch_id = 1;
ROLLBACK TO SAVEPOINT before_change;
COMMIT;
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT dispatch_id FROM dispatches
WHERE branch = 'West' AND status = 'open';
The grouped query filters rows before aggregation and groups before applying HAVING. The second sort key makes tied counts deterministic. COUNT(*) counts rows, while COUNT(column) counts non-null values. Use IS NULL to test for a missing value; = NULL does not produce true.
Index choices
The partial B-tree contains open dispatches and supports the matching branch filter. The expression index targets extracted priority text. The GIN index serves supported JSONB operations such as the containment predicate shown. Creating all three is a syntax demonstration, not a recommendation to add every index to an application.
Each one costs storage and write work.
An index’s existence does not guarantee its use. Inspect the plan and data distribution in the B-tree lesson and partial and expression indexes. A multicolumn index must be considered against its operator classes, column order and actual query predicates.
Transactions and plans
ROLLBACK TO SAVEPOINT undoes the update while keeping the transaction open. COMMIT ends the transaction. In a failed transaction, recover with a rollback before issuing unrelated commands. Changes to sequence counters are an exception to ordinary rollback expectations.
EXPLAIN estimates a plan. Adding ANALYZE runs the statement, including writes when you use it on a data-changing statement. In a scratch database, wrap an exploratory write in BEGIN and ROLLBACK when appropriate.
JSONB and security
-> returns a JSON value; ->> returns text. @> tests containment. A missing key and a JSON null are different cases, so choose the operator before choosing a default value. Practice those cases in JSONB operators.
Permissions and row policies are separate checks. A role still needs the table privilege; a row policy restricts eligible rows. Table owners normally bypass row policies, and superusers and BYPASSRLS roles bypass them. Test with the intended application role in row-level security.
For a runnable task with expected results, open PostgreSQL exercises. The course adds checked practice; it is not a terminal with every psql command.