Postgres Core API Surface

postgres-core-api-surface

Quick Reference :

PostgreSQL's command surface splits into four orthogonal layers and one out-of-band tool channel :

• DDL : CREATE / ALTER / DROP / TRUNCATE for schema objects (tables, indexes, views, roles, types, functions, policies, publications, subscriptions).
• DML : SELECT / INSERT / UPDATE / DELETE / MERGE (v15+) / COPY for data.
• DCL : GRANT / REVOKE / ALTER DEFAULT PRIVILEGES for access control.
• TCL : BEGIN / COMMIT / ROLLBACK / SAVEPOINT / RELEASE / ROLLBACK TO / SET TRANSACTION for transaction boundaries.
• psql meta-commands : backslash commands (\d, \dt, \df, \du, \timing, \watch, \copy, \i, \e, \!) drive the database operationally.

When you need to introspect the live database, prefer pg_catalog over information_schema : pg_catalog is faster, version-stable for PostgreSQL features, and exposes PostgreSQL-specific columns (pg_class.reltuples, pg_attribute.attnotnull). Use information_schema only when portability across SQL engines is a hard requirement.

When To Use This Skill :

ALWAYS use this skill when :

• A task says "introspect schema", "list tables", "find privileges", "inspect indexes" : pick pg_catalog or information_schema correctly.
• You need to choose between INSERT ... ON CONFLICT and MERGE (v15+) for upsert.
• You need TCL semantics : nested transactions via SAVEPOINT, isolation levels via SET TRANSACTION.
• You need a psql meta-command equivalent to a SQL query (e.g. \d table is shorthand, not magic).

NEVER use this skill for :

• Performance tuning : see postgres-core-mvcc-wal and postgres-impl-explain.
• Index access methods (B-tree vs GIN vs BRIN) : see postgres-syntax-indexing.
• Role / privilege design : see postgres-impl-security and postgres-impl-rls.
• Deep pl/pgSQL programming : out-of-scope per D-019.

Decision Trees :

Catalog selection : pg_catalog vs information_schema :

Need PostgreSQL-specific info (reltuples, attnotnull, pg_index.indnatts, pg_authid.rolcanlogin)?
  -> pg_catalog (always faster, no view overhead, version-stable per release)

Need portability across PostgreSQL / MySQL / SQL Server / Oracle?
  -> information_schema (SQL standard, slower because it is a view over pg_catalog)

Need extension metadata (pg_extension, pg_proc.proowner)?
  -> pg_catalog (information_schema does not expose extensions)

Need statistics or live activity (pg_stat_user_tables, pg_stat_activity, pg_settings)?
  -> pg_catalog ONLY (information_schema has no equivalent)

Upsert : INSERT ... ON CONFLICT vs MERGE :

Single row or small batch, conflict target is one unique index?
  -> INSERT ... ON CONFLICT (target) DO UPDATE SET col = EXCLUDED.col

Need to atomically INSERT OR UPDATE OR DELETE based on join shape?
  -> MERGE (v15+)

Need full-sync pattern (DELETE rows in target that no longer exist in source)?
  -> MERGE ... WHEN NOT MATCHED BY SOURCE THEN DELETE (v16+, BY TARGET v17+)

Need rows returned with the action taken (INSERT vs UPDATE vs DELETE)?
  -> MERGE ... RETURNING merge_action(), * (v17+)

Target is a materialized view, foreign table, or has rules?
  -> NEITHER : MERGE rejects these; ON CONFLICT requires NOT DEFERRABLE unique constraint.

Transaction boundary tool :

Single statement, no rollback needed?
  -> autocommit (no explicit BEGIN)

Multi-statement atomicity, all-or-nothing?
  -> BEGIN; ...; COMMIT;  (or ROLLBACK; on error)

Need partial rollback to a checkpoint inside a transaction?
  -> SAVEPOINT sp_1; ...; ROLLBACK TO SAVEPOINT sp_1; (then RELEASE sp_1 or COMMIT)

Need to isolate from concurrent writes (phantom reads, lost updates)?
  -> BEGIN ISOLATION LEVEL { REPEATABLE READ | SERIALIZABLE };

psql for ops vs SQL for code :

Interactive inspection during a session?
  -> Backslash command (\dt, \df, \dn, \du, \dx, \l)

Inside an application, script, or CI job?
  -> Plain SQL against pg_catalog (psql meta-commands are NOT portable across drivers)

One-off bulk load from a local file?
  -> \copy (client-side, runs with client's file permissions)

Server-side bulk load from a server-local file?
  -> COPY (server-side, requires pg_read_server_files role or superuser)

Patterns :

Pattern 1 : Atomic upsert via INSERT ... ON CONFLICT

ALWAYS : name a conflict target (column list or ON CONSTRAINT name). NEVER : omit the conflict target ; PostgreSQL requires it to pick the arbiter index.

INSERT INTO products (sku, name, price)
VALUES ('SKU-001', 'Widget', 9.99)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name,
      price = EXCLUDED.price
  WHERE products.price <> EXCLUDED.price
RETURNING sku, (xmax = 0) AS inserted;

WHY : EXCLUDED is the row proposed for insertion. The WHERE clause skips no-op updates so heap pressure stays low. xmax = 0 distinguishes INSERT (true) from UPDATE (false) in RETURNING.

Pattern 2 : Full-sync MERGE (v17+ with RETURNING merge_action())

ALWAYS : ensure the ON clause produces at most one source row per target row. NEVER : use MERGE on materialized views, foreign tables, or rule-defined tables : rejected with error.

MERGE INTO inventory tgt
USING staging_inventory src
  ON tgt.sku = src.sku
WHEN MATCHED AND tgt.qty <> src.qty THEN
  UPDATE SET qty = src.qty
WHEN NOT MATCHED BY TARGET THEN
  INSERT (sku, qty) VALUES (src.sku, src.qty)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
RETURNING merge_action(), tgt.sku, tgt.qty;

WHY : merge_action() returns 'INSERT', 'UPDATE', or 'DELETE' per affected row. WHEN NOT MATCHED BY SOURCE (v16+) lets you remove rows that disappeared from staging.

Pattern 3 : Nested transaction via SAVEPOINT

ALWAYS : RELEASE savepoints you no longer need, otherwise they linger and consume memory. NEVER : assume a ROLLBACK TO SAVEPOINT ends the transaction. It only rewinds to the savepoint; you must still COMMIT or ROLLBACK.

BEGIN;
  INSERT INTO orders (customer_id, total) VALUES (42, 100.00);
  SAVEPOINT before_lines;
    INSERT INTO order_lines (order_id, sku, qty) VALUES (currval('orders_id_seq'), 'BAD-SKU', 1);
    -- raises foreign_key_violation (SQLSTATE 23503)
  ROLLBACK TO SAVEPOINT before_lines;
  -- order header is preserved, lines are not
COMMIT;

WHY : a single error inside a transaction puts the whole transaction in aborted state. SAVEPOINT lets you recover from a sub-step failure without losing earlier work.

Pattern 4 : Catalog query for table size + index list

ALWAYS : join pg_class to pg_namespace and filter nspname to scope the schema. NEVER : assume oid is the join key for pg_class.relfilenode : relfilenode changes across VACUUM FULL / REINDEX.

SELECT
  n.nspname        AS schema_name,
  c.relname        AS table_name,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
  c.reltuples::bigint AS estimated_rows,
  (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS index_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'                       -- ordinary table
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

WHY : pg_class.relkind codes are r (table), i (index), S (sequence), v (view), m (materialized view), c (composite type), p (partitioned table), f (foreign table). pg_total_relation_size includes heap + indexes + TOAST.

Pattern 5 : psql introspection shortcuts that match pg_catalog queries

ALWAYS : know which catalog query a backslash command represents — it makes scripting easier. NEVER : embed psql meta-commands in application code ; they are an interactive shell feature only.

\dt schema.*       -> SELECT relname FROM pg_class JOIN pg_namespace ON ... WHERE relkind = 'r'
\df schema.*       -> SELECT proname FROM pg_proc JOIN pg_namespace ON ...
\du                -> SELECT rolname FROM pg_authid (or pg_roles if not superuser)
\dn+               -> SELECT nspname, nspowner FROM pg_namespace
\dx                -> SELECT extname, extversion FROM pg_extension
\l                 -> SELECT datname, datacl FROM pg_database
\d table_name      -> joins pg_class + pg_attribute + pg_constraint + pg_index
\timing on         -> client-side timing (no server impact)
\watch 2           -> re-run query buffer every 2 seconds

Pattern 6 : DCL : grant table privileges to a role

ALWAYS : grant on schema first (USAGE), then on objects. NEVER : forget that future tables need ALTER DEFAULT PRIVILEGES ; existing grants do not propagate.

GRANT USAGE ON SCHEMA app TO app_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_reader;

WHY : GRANT ... ON ALL TABLES is a one-time snapshot. ALTER DEFAULT PRIVILEGES makes the grant apply to future tables created in that schema.

Anti-Patterns :

(Short list, full content in references/anti-patterns.md)

• Querying information_schema for everything : 5-50x slower than pg_catalog. See references/anti-patterns.md.
• INSERT ... ON CONFLICT without conflict target : SQLSTATE 42601 syntax_error.
• MERGE on a join that returns multiple source rows per target row : SQLSTATE 21506 triggered_data_change_violation (cardinality violation).
• Embedding \d or \dt in an application : meta-commands are psql-only and not understood by the wire protocol.
• Forgetting ALTER DEFAULT PRIVILEGES after GRANT ON ALL TABLES : new tables created later have no grant.
• ROLLBACK TO SAVEPOINT then assuming transaction ended : transaction still open ; must COMMIT or ROLLBACK explicitly.
• Mixing \copy (client-side) and COPY (server-side) without checking file location : COPY 'path' reads from server disk, \copy from client disk.
• Using pg_class.relfilenode as a stable identifier : changes after VACUUM FULL / REINDEX / CLUSTER. Use oid instead.

Reference Links :

• references/methods.md : Full DDL/DML/DCL/TCL syntax tables, system catalog map (pg_class / pg_attribute / pg_index / pg_constraint / pg_namespace / pg_proc / pg_authid / pg_stat_* / pg_settings), psql meta-command reference.
• references/examples.md : Working code samples for upsert, MERGE (v15/v16/v17 deltas), savepoints, catalog introspection queries, DCL grant matrices, version-annotated.
• references/anti-patterns.md : Anti-patterns with cause + symptom + fix-pattern + SQLSTATE codes (42601, 23503, 21506, 42501, 0A000).

See Also :

• postgres-core-mvcc-wal : transaction snapshots, isolation levels, locking — backs TCL semantics.
• postgres-core-version-matrix : v15 / v16 / v17 feature deltas — confirm MERGE feature availability.
• postgres-syntax-dml-upsert : deep dive on INSERT ... ON CONFLICT and MERGE patterns.
• postgres-impl-security : role design, privilege grants, predefined roles.
• postgres-errors-sqlstate : SQLSTATE code reference for the anti-patterns above.
• Vooronderzoek section : §2 Full SQL Surface, §22 Implications for Masterplan.
• Official docs : https://www.postgresql.org/docs/17/sql-merge.html , https://www.postgresql.org/docs/17/sql-insert.html , https://www.postgresql.org/docs/17/app-psql.html , https://www.postgresql.org/docs/17/catalogs.html