SQL Statements

Every example on this page runs against this small demo schema, top to bottom - create it first:

CREATE TABLE customers  (id BIGINT PRIMARY KEY, name TEXT, country TEXT, tier TEXT);
CREATE TABLE orders     (id BIGINT PRIMARY KEY, customer_id BIGINT, amount DOUBLE PRECISION,
                         status TEXT, owner TEXT, order_date DATE, created_at TIMESTAMP);
CREATE TABLE products   (id BIGINT PRIMARY KEY, name TEXT, price DOUBLE PRECISION,
                         category TEXT, brand TEXT, sales BIGINT);
CREATE TABLE employees  (id BIGINT PRIMARY KEY, name TEXT, manager_id BIGINT,
                         department TEXT, salary DOUBLE PRECISION);
CREATE TABLE suppliers  (id BIGINT PRIMARY KEY, name TEXT);
CREATE TABLE archive    (id BIGINT, customer_id BIGINT, amount DOUBLE PRECISION,
                         status TEXT, owner TEXT, order_date DATE, created_at TIMESTAMP);
CREATE TABLE inventory  (sku TEXT, quantity BIGINT);
CREATE TABLE shipments  (sku TEXT, quantity BIGINT);
CREATE TABLE jobs       (id BIGINT, status TEXT, created_at TIMESTAMP);
CREATE TABLE accounts   (id BIGINT PRIMARY KEY, balance DOUBLE PRECISION);
CREATE TABLE important_data (id BIGINT);

Data Definition Language (DDL)

CREATE TABLE

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email TEXT,
    balance DOUBLE PRECISION DEFAULT 0.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Supported constraints: PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK

CREATE TABLE ... WITH (home_region = ...)

On a cluster, home a table's voters in a specific region so its transactions commit at region-local quorum latency instead of paying a wide-area round trip on every write (see Multi-region):

CREATE TABLE eu_customers (
    id BIGINT PRIMARY KEY,
    name TEXT,
    country TEXT
) WITH (home_region = 'eu-central');

home_region takes a quoted region name, matched case-sensitively against the region your nodes are labelled with, or the keyword 'GLOBAL' (matched case-insensitively), which spreads the table's voters across every region instead of homing them in one:

CREATE TABLE product_catalog (
    sku TEXT PRIMARY KEY,
    name TEXT,
    price NUMERIC
) WITH (home_region = 'GLOBAL');

Omit home_region entirely and the table homes wherever it was created: the creating node's own configured region, or nowhere in particular on a regionless cluster, exactly as before this option existed. An empty string (home_region = '') is refused. On a single-node instance home_region parses but has no placement to affect; it only matters once [cluster] is configured.

CREATE TABLE AS SELECT

CREATE TABLE active_users AS
SELECT id, username, balance
FROM users
WHERE balance > 0;

CREATE TABLE ... PARTITION BY

Partition a large table by range, list, or hash:

CREATE TABLE events (
    id BIGINT,
    created_at DATE,
    payload TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Attach an existing table as a partition, or detach one
CREATE TABLE events_2025 (id BIGINT, created_at DATE, payload TEXT);
ALTER TABLE events ATTACH PARTITION events_2025
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
ALTER TABLE events DETACH PARTITION events_2024;

An UPDATE or DELETE of a partitioned table is one statement across all its partitions: inside a transaction block every partition's change commits or rolls back together. An UPDATE that changes a row's partition key moves the row to the partition its new key belongs to, as PostgreSQL does; a key no partition accepts fails the statement with SQLSTATE 23514 and changes nothing.

DROP TABLE

CREATE TABLE scratch (id BIGINT);
DROP TABLE scratch;
DROP TABLE IF EXISTS scratch;   -- no error when it is already gone

TRUNCATE

TRUNCATE TABLE orders;

Removes every row instantly, along with the contents of every index on the table (a vector index keeps its definition and starts over empty).

On a cluster, TRUNCATE of a distributed table empties it on every node that holds or replicates it, whichever node you run it on. It first waits, on every node, for the transactions still using the table to finish and holds back new ones, then empties every node's copy at one cluster timestamp. A node that cannot be reached fails the statement with the node's name and the reason; nothing is left locked, and running the statement again finishes the job. How long it waits for one node is [cluster] truncate_node_timeout (see Config File).

CREATE INDEX

Index the columns you query most; the optimizer selects the access path automatically.

-- Single-column index
CREATE INDEX idx_users_email ON users (email);

-- Composite index (prefix matching, left to right)
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- Partial index: only the rows matching the predicate
CREATE INDEX idx_active_orders ON orders (customer_id) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users (lower(email));

Index features:

• Composite indexes with prefix matching on left-to-right columns
• Partial indexes (WHERE) and expression indexes
• Index-only scans when every projected column is in the index
• Cost-based selection: the optimizer chooses an index or a full scan automatically

GIN Indexes (USING gin)

An inverted index over the contents of a JSONB or array column: each row is indexed under every key and value (or array element) it holds, so a containment or key-existence filter finds its rows without reading the whole table.

CREATE TABLE devices (id INT PRIMARY KEY, tags JSONB);
INSERT INTO devices VALUES
    (1, '{"elec": true, "meter": true, "site": "north"}'),
    (2, '{"point": true, "zone": true, "unit": "kW"}'),
    (3, '{"elec": true, "equip": {"ahu": {"fan": 3}}}');

-- Default operator class (jsonb_ops): answers @>, <@ and ?
CREATE INDEX devices_tags_idx ON devices USING gin (tags);

SELECT id FROM devices WHERE tags @> '{"elec": true, "meter": true}';
SELECT id FROM devices WHERE tags ? 'zone';

-- jsonb_path_ops: a smaller index for containment only
CREATE INDEX devices_tags_path_idx ON devices USING gin (tags jsonb_path_ops);

Operator class	Column type	Default	Operators the index answers
jsonb_ops	JSONB	yes	@>, <@, ?
jsonb_path_ops	JSONB	no	@>, <@
array_ops	any array	yes	none yet: the array operators @>, <@ and && answer by reading the table, and no query uses this index today

• Every row the index offers is rechecked against the whole filter, so the answer is always the one a full scan gives. EXPLAIN shows GIN Index Scan with the operator classes, its Index Cond and its Recheck Cond.
• The optimizer picks the index or a full scan from the index's own entry counts: a filter that matches most of the table reads the table instead.
• Partial, expression (USING gin ((tags -> 'equip'))) and multi-column GIN indexes work; a partial index is used only when the query implies its WHERE.
• Every write keeps the index current, and it is rebuilt from the table at startup.
• ?| and ?& are not available yet, so no query uses a GIN index for them.
• Inside a REPEATABLE READ or SERIALIZABLE transaction a GIN-planned filter is answered by scanning the table as of the transaction's snapshot: the index holds only the latest version of each row.

A GIN index cannot be UNIQUE, and a type with no GIN operator class is refused with PostgreSQL's own error:

ERROR:  data type integer has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

DROP INDEX

DROP INDEX idx_users_email;

Vector Indexes (USING anode / USING manode)

An approximate nearest-neighbour index over a VECTOR, HALFVEC, SPARSEVEC or BIT column (see Data Types and Vector Search). Two access methods: anode on a single-node table, manode on a distributed one.

CREATE TABLE articles (id BIGINT PRIMARY KEY, title TEXT, embedding VECTOR(3));
INSERT INTO articles VALUES
    (1, 'a', '[1,0,0]'), (2, 'b', '[0,1,0]'), (3, 'c', '[1,1,0]');

-- Default opclass (vector_l2_ops), default options
CREATE INDEX articles_embedding_idx ON articles USING anode (embedding);

-- An explicit opclass and a handful of options
CREATE INDEX articles_embedding_cos_idx ON articles
    USING anode (embedding vector_cosine_ops)
    WITH (memory_budget = 268435456, over_fetch = 4, probe_permille = 200);

DROP INDEX articles_embedding_cos_idx;

The column needs a declared dimension (VECTOR(3), not a bare VECTOR); a bare, dimensionless column is refused with column does not have dimensions. The opclass picks the metric and defaults to <type>_l2_ops (<type>_hamming_ops for BIT) when omitted:

Type	Opclasses (metric)
VECTOR, HALFVEC, SPARSEVEC	*_l2_ops (default), *_cosine_ops, *_ip_ops, *_l1_ops
BIT	*_hamming_ops (default), *_jaccard_ops

BIT VARYING has no ANN opclass and is refused. Each type also has a dimension ceiling for an ANN index: 2,000 for VECTOR, 4,000 for HALFVEC, 64,000 for BIT, and vector.sparsevec_index_max_dims for SPARSEVEC (2,000 by default, pgvector's limit; ALTER SYSTEM SET vector.sparsevec_index_max_dims raises it for indexes created after it). anode refuses a distributed table (index method anode is not supported on a distributed table; use manode instead); manode works on either. An index keys on one vector column, or on one expression that casts a single column to a vector type with a declared dimension (USING anode ((embedding::halfvec(3)) halfvec_l2_ops), half-precision indexing), and a top-k search, a distance threshold or a LATERAL nearest-neighbour join whose distance is written over that same expression is served by it on the same terms as by a plain column index. A partial index (WHERE ...) serves a search whose own filter implies the index's condition, never a LATERAL nearest-neighbour join. A composite (multi-column) vector index is not supported: create one index per column.

WITH (...) options, all optional:

Option	Type	Effect
memory_budget	bytes, ≥ 1	This index's own memory ceiling, in place of [vector] index_memory_budget. Set at CREATE INDEX; ALTER INDEX ... SET resizes it (takes effect at the index's next background regrow, see below) and REINDEX rebuilds it at once
arena_slots	integer, ≥ 1	The arena's promised vector capacity, in place of the automatic live_rows * arena_growth_factor sizing. Set at CREATE INDEX; ALTER INDEX ... SET resizes it the same way
over_fetch	integer, ≥ 1	Candidates fetched per requested row before the exact re-rank (default 2)
paused_compaction	boolean	Pause this index's own background compaction (true/false/on/off/1/0; a bare paused_compaction means true)
probe_permille, near_permille	integer, 1-1000	Share of the live set, and the nearest cells within it, a search sweeps
walk_budget, beam, rank_head	integer, ≥ 1	The graph-walk candidate budget, beam width, and ranked-cell count
rerank_extra	integer, ≥ 0	Extra candidates carried into the exact re-rank

An unrecognized option, including pgvector's HNSW/IVFFlat options (m, ef_construction, lists), is refused with unrecognized parameter "<key>"; a value outside a key's range is refused with value <v> out of bounds for option "<key>": must be between <min> and <max>.

hnsw and ivfflat (pgvector's own index methods) are refused by name:

CREATE INDEX bad_idx ON articles USING hnsw (embedding vector_l2_ops);
-- ERROR: index method hnsw is not available; use anode on one node or manode on a
-- distributed table

CREATE INDEX CONCURRENTLY is accepted for a vector index: the keyword is honored (the build never blocks writers) rather than needed.

ALTER INDEX ... SET / RESET

Change a live search knob, over_fetch, or paused_compaction without rebuilding:

ALTER INDEX articles_embedding_idx SET (probe_permille = 150, beam = 32);
ALTER INDEX articles_embedding_idx RESET (probe_permille, beam);

Each of those takes effect at once, reported by a NOTICE per option naming so: option "probe_permille" on index "articles_embedding_idx" takes effect live. RESET takes a bare option name with no value; a boolean option is set through SET the same way CREATE INDEX ... WITH accepts it (true/false/on/off/1/0).

memory_budget and arena_slots resize the arena instead of taking effect at once. SET (memory_budget = <bytes>) (or arena_slots = <count>) is checked against the rows already indexed and, when it fits, recorded and applied at the index's next background regrow (NOTICE: takes effect next regrow); a value too small for what is already indexed is refused, naming the row count instead of silently rounding up:

ERROR:  index "articles_embedding_idx" cannot set "memory_budget" to 1024: the 3 rows
already indexed would not fit; REINDEX with a smaller promise instead

RESET (memory_budget) and RESET (arena_slots) forget the option, as RESET does for any storage parameter. The arena keeps the size it has, since a regrow never opens it smaller than it is, and the next REINDEX sizes the index from the node's own settings again:

NOTICE:  option "memory_budget" on index "articles_embedding_idx" takes effect next reindex

REINDEX

Rebuild an index from the table's current rows. A vector index is rebuilt into a fresh arena, sized from the table's current row count; any other index (including GIN) is rebuilt through the same path CREATE INDEX uses. Either way the old index keeps answering until the new one replaces it whole.

REINDEX INDEX articles_embedding_idx;
REINDEX TABLE articles;      -- rebuilds every index on the table
REINDEX DATABASE scramdb;    -- superuser only

CONCURRENTLY is accepted and ignored (a vector REINDEX never blocks readers: the old arena keeps serving until the new one swaps in atomically).

A vector REINDEX that meets another rebuild of the same index (a second REINDEX, or the automatic regrow) waits for it to finish and then rebuilds, as PostgreSQL does; the wait ends with canceling statement due to lock timeout (55P03) once lock_timeout passes, or with 57014 at statement_timeout.

ALTER SYSTEM SET / RESET

ALTER SYSTEM in this release covers vector.<key> settings only (superuser required); every other section is a named follow-up. It writes the value into the catalog store (so it survives a restart) and applies it at once:

ALTER SYSTEM SET vector.over_fetch = 4;
ALTER SYSTEM RESET vector.over_fetch;

-- Node-scoped keys (memory_budget_total, maintenance_workers, build_workers,
-- rerank_prefetch, simd_level, sample_min_interval_ms) can target one node in a
-- cluster; omitted, they target the issuing node
ALTER SYSTEM SET vector.simd_level = 'avx2' ON NODE 'node-2';
ALTER SYSTEM RESET vector.simd_level;

SELECT pg_reload_conf();

See Configuration: Vector Indexes for every vector.<key> name, its accepted values, and how it differs from the [vector] config-file key of the same name (three settings are thousandths integers here rather than the config file's plain fraction: vector.selectivity_boundary_permille, vector.threshold_slack_permille, vector.compact_tombstone_ratio_permille).

EXPLAIN

EXPLAIN SELECT id FROM articles ORDER BY embedding <-> '[1,0,0]' LIMIT 5;

A query the index accelerates shows the scan and the tier it chose:

AnnIndexScan on articles using articles_embedding_idx  (tier: exact, k: 5)

A query over an indexed column that the index does not serve (the index is still building, regrowing or invalid, the kill switch is on, or the shape does not match) shows a plain scan with one plain sentence naming the reason instead, appended to the same line:

Seq Scan on articles  (cols: *)  (index not used: <reason>)

EXPLAIN ANALYZE adds, per vector index touched, the candidates fetched, widening rounds taken, rows carried into the exact re-rank, index calls, and whether the answer came back underfilled (fewer rows than requested).

DROP TABLE articles;

ALTER TABLE

ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users ALTER COLUMN balance SET NOT NULL;
ALTER TABLE users RENAME TO members;
ALTER TABLE members RENAME TO users;

Re-home an existing distributed table (cluster mode only), moving its voters to a different region in the background:

ALTER TABLE eu_customers SET (home_region = 'us-east');
ALTER TABLE product_catalog SET (home_region = 'GLOBAL');

The move is asynchronous: the statement returns once the new home is recorded, and scram.shards.pending_owners shows each affected bucket still converging until it completes. SET (...) accepts only the home_region key in this form; any other key inside the parentheses is refused. It requires a table that already has shard buckets (a PRIMARY KEY table assigned to a shard map).

On a cluster, every form of ALTER TABLE can be issued through any node and applies on every node, in order with the table's rows; see Changing a table on a cluster.

CREATE / DROP VIEW

CREATE VIEW high_value_orders AS
SELECT * FROM orders WHERE amount > 10000;

DROP VIEW high_value_orders;

Views are expanded inline at query time.

CREATE MATERIALIZED VIEW

A materialized view stores its results so later reads are fast. Recompute it with REFRESH.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_sales;

-- Create the view without populating it
CREATE MATERIALIZED VIEW monthly_sales_pending AS
SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY 1
WITH NO DATA;

CREATE MATERIALIZED VIEW completes with SELECT n, the number of rows it stored, as CREATE TABLE AS does; WITH NO DATA completes with the bare verb. A name another relation already holds is refused with 42P07, and IF NOT EXISTS skips the statement with a notice.

REFRESH MATERIALIZED VIEW CONCURRENTLY parses but is not supported; it is refused at execution time. Use a plain REFRESH MATERIALIZED VIEW instead.

CREATE SCHEMA

CREATE SCHEMA analytics;
CREATE TABLE analytics.reports (id BIGINT PRIMARY KEY, body TEXT);

-- Control how unqualified names are resolved
SET search_path TO analytics, public;

CREATE TYPE and CREATE DOMAIN

-- Enumerated type
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');

-- Composite type
CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT);

-- Domain: a base type plus a constraint
CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);

ALTER DOMAIN takes every PostgreSQL form: SET DEFAULT / DROP DEFAULT, SET NOT NULL / DROP NOT NULL, ADD a CHECK (also NOT VALID) or NOT NULL constraint, DROP CONSTRAINT [IF EXISTS], RENAME CONSTRAINT, VALIDATE CONSTRAINT, RENAME TO and OWNER TO. A new constraint is checked first against every column the domain types, and refused with PostgreSQL's 23502 or 23514 naming the column when a stored value breaks it. SET SCHEMA accepts only public, where every user type lives, and refuses any other schema with 0A000.

CREATE DOMAIN small_qty AS INTEGER;
ALTER DOMAIN small_qty ADD CONSTRAINT small_qty_pos CHECK (VALUE > 0);
ALTER DOMAIN small_qty SET DEFAULT 1;
ALTER DOMAIN small_qty RENAME CONSTRAINT small_qty_pos TO small_qty_positive;
DROP DOMAIN small_qty;

COLLATE is accepted wherever PostgreSQL takes it: on a column (CREATE TABLE, ALTER TABLE ... ADD COLUMN), on a domain and on a composite type's field. The collation must exist (42704 otherwise) and the type must be collatable (42804 otherwise). The collations that exist are default (the database's C.UTF-8), C and POSIX, and all three order text by its bytes. A column keeps its collation, which pg_attribute.attcollation reports; a domain's is pg_type.typcollation.

CREATE TABLE coll_demo (code TEXT COLLATE "C", label TEXT);
DROP TABLE coll_demo;

Roles and Row-Level Security

-- Role-based access control
CREATE ROLE analyst LOGIN PASSWORD 'secret';
GRANT SELECT ON orders TO analyst;

-- Row-level security: each role sees only its own rows
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_by_owner ON orders
    USING (owner = current_user);

Functions and Procedures

Write functions and procedures in PL/pgSQL:

CREATE FUNCTION total_for(customer BIGINT) RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = customer);
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE archive_old_orders(cutoff DATE) AS $$
BEGIN
    INSERT INTO archive SELECT * FROM orders WHERE order_date < cutoff;
    DELETE FROM orders WHERE order_date < cutoff;
END;
$$ LANGUAGE plpgsql;

A RETURN expr body implies LANGUAGE sql, the SQL-standard form PostgreSQL 14 added, so the clause can be omitted there:

CREATE FUNCTION double_it(x INT) RETURNS INT
    STRICT IMMUTABLE PARALLEL SAFE LEAKPROOF
    RETURN x * 2;

The inference is deliberately narrow: only a RETURN expr body implies a language. A quoted AS '...' body without LANGUAGE stays an error, because the body text alone cannot say whether it is SQL or PL/pgSQL. The routine attributes above (STRICT, IMMUTABLE/STABLE/VOLATILE, PARALLEL, LEAKPROOF, SECURITY) are accepted; LEAKPROOF and PARALLEL are planner hints this engine does not consult today.

A function call runs once for each row that reaches it, as in PostgreSQL: never for a row a WHERE removed or a row whose CASE branch, later COALESCE argument or AND/OR right side does not reach it, so it never raises for such a row. A VOLATILE function runs for every row it reaches even with constant arguments, and under a LIMIT only for the rows the LIMIT reads. A function called in a statement's own RETURNING list, VALUES row, ON CONFLICT SET list, column DEFAULT or CHECK constraint runs inside that statement's transaction; a DEFAULT runs for each row an INSERT fills with it, never at CREATE TABLE.

Routine names are currently unique by NAME, not by signature: two functions of the same name with different argument types (PostgreSQL overloading) are not supported yet, and the second CREATE is refused rather than silently replacing the first. Use CREATE OR REPLACE to redefine one, or give the variants distinct names.

You can also write user-defined functions in JavaScript, TypeScript, Rust, Go, C, C++, Python, and Ruby.

A statement that fails inside a PL/pgSQL function, procedure, or DO block keeps its original SQLSTATE and message, with a CONTEXT: SQL statement "..." at <position> line appended naming the failing statement, instead of being flattened into a generic block failure. A caller can still branch on the original error code from inside an EXCEPTION block.

CREATE TRIGGER

CREATE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION touch_updated_at();

Row-level, statement-level, INSTEAD OF, and event triggers are all supported.

Inside a PL/pgSQL function or procedure body, PostgreSQL's postfix null tests also parse: expr ISNULL and expr NOTNULL are equivalent to expr IS NULL and expr IS NOT NULL.

CREATE TABLE flags (id BIGINT PRIMARY KEY, note TEXT, note_set BOOLEAN);

CREATE FUNCTION flags_track_note() RETURNS trigger AS $$
BEGIN
    IF NEW.note ISNULL THEN
        NEW.note_set := false;
    ELSIF NEW.note NOTNULL THEN
        NEW.note_set := true;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER flags_track_note_t
    BEFORE INSERT ON flags
    FOR EACH ROW
    EXECUTE FUNCTION flags_track_note();

INSERT INTO flags (id, note) VALUES (1, NULL), (2, 'hello');

CREATE DATABASE (clone and branch)

There are three forms:

-- Empty new database
CREATE DATABASE production;
CREATE DATABASE IF NOT EXISTS production;   -- no error when it already exists

-- Instant branch of another database, present state
CREATE DATABASE staging CLONE production;
CREATE DATABASE staging_qa TEMPLATE production;   -- TEMPLATE is a synonym for CLONE

Branching as of a past point in time needs a configured backup location and a base backup taken to it first (see Backups):

CREATE DATABASE audit FROM production AT TIMESTAMP '2026-07-01 00:00:00';

FROM is only valid together with AT TIMESTAMP; a bare FROM production with no timestamp does not parse. Use CLONE (or TEMPLATE) for an instant branch of the current state.

Both forms work on a cluster, through any node. A CLONE forks every shard group of the parent at one commit position every group shares, so a transaction that wrote several groups is in the branch on all of them or on none; it needs a quorum of each group, not every member. FROM ... AT TIMESTAMP on a cluster reads the cluster's WAL archive instead of a base backup; see Branching.

CREATE DATABASE cannot run inside an explicit transaction block (BEGIN; CREATE DATABASE ...; is refused).

Statements on a non-default database connection

Connecting with dbname= set to any database other than the server's default (every branch and clone is such a database) scopes each statement to that database's own tables. Coverage:

Works, scoped to the connected database	Refused loudly (never silently run against the default database)
Reads (SELECT, CTEs, cursors), INSERT, UPDATE, DELETE, MERGE	CREATE VIEW, CREATE SCHEMA
COPY in every form, including COPY ... FROM STDIN	COMMENT ON
CREATE TABLE, ALTER TABLE, CREATE INDEX, ALTER INDEX, CREATE SEQUENCE, DROP (tables, indexes), TRUNCATE, ANALYZE	VACUUM
GRANT/REVOKE on tables (privileges are per-database: a grant on a branch covers the branch's table only)	GRANT/REVOKE on schemas, sequences, or databases
CREATE/ALTER/DROP ROLE (roles are instance-wide, PostgreSQL semantics), CREATE DATABASE (a branch of a branch included), DROP DATABASE, transaction control, SET/SHOW	Any other statement kind

A refused statement fails with an error naming the statement kind; run it on a connection to the default database instead.

COMMENT ON

COMMENT ON TABLE orders IS 'Customer orders, one row per order';
COMMENT ON COLUMN orders.amount IS 'Order total in USD';

A comment belongs to the object it was set on: it follows a table through ALTER TABLE ... RENAME, goes with it on DROP, and never shows on another schema's object of the same name. Read comments back as in PostgreSQL, through pg_description or the functions over it, whose result columns carry the function's name:

SELECT c.relname, obj_description(c.oid, 'pg_class'), col_description(c.oid, 2)
FROM pg_class c WHERE c.relname = 'orders';

---

Data Manipulation Language (DML)

SELECT

-- Basic
SELECT id, name, price FROM products WHERE price > 100;

-- Joins
SELECT o.id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.amount > 500;

-- Aggregation
SELECT department, AVG(salary), COUNT(*)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- COUNT/SUM DISTINCT dedups by value, for every numeric type
SELECT department, COUNT(DISTINCT salary)
FROM employees
GROUP BY department;

-- A scalar function wrapping an aggregate
SELECT department, ROUND(AVG(salary), 2)
FROM employees
GROUP BY department;

-- HAVING with no GROUP BY: the whole result is one group
SELECT 1 FROM employees HAVING false;   -- zero rows
SELECT 1 FROM employees HAVING true;    -- exactly one row, whatever the input size

-- Window functions
SELECT name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;

-- Window frames: RANGE and GROUPS group tied peer rows together,
-- unlike ROWS, which treats every row as its own frame position
SELECT department, salary,
       SUM(salary) OVER (
           PARTITION BY department ORDER BY salary
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees;

-- RANGE takes a typed offset: a plain number over a numeric ORDER BY
-- key, an INTERVAL over a DATE, TIME or TIMESTAMP key
SELECT id, order_date, amount,
       SUM(amount) OVER (
           ORDER BY order_date
           RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
       ) AS trailing_2_day_total
FROM orders;

SELECT department, salary,
       COUNT(*) OVER (
           PARTITION BY department ORDER BY salary
           GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS peer_group_count
FROM employees;

-- LIMIT and OFFSET accept a constant, a bind parameter, or an
-- uncorrelated scalar subquery, resolved once before the statement runs
SELECT * FROM orders ORDER BY amount DESC LIMIT (SELECT 5);

-- FETCH FIRST / FETCH NEXT n ROWS ONLY is the SQL-standard spelling of
-- LIMIT, and keeps its bound over a set operation or a derived VALUES
SELECT * FROM orders ORDER BY amount DESC OFFSET 2 ROWS FETCH FIRST 5 ROWS ONLY;
SELECT * FROM (SELECT amount FROM orders UNION ALL SELECT amount FROM archive) u
ORDER BY amount DESC FETCH FIRST 1 ROWS ONLY;

-- CTEs (WITH clause), including WITH RECURSIVE
WITH RECURSIVE org_chart AS (
    SELECT id, manager_id, name FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, e.name
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;

-- LATERAL join: a subquery that references the row on its left
SELECT c.name, recent.amount
FROM customers c
JOIN LATERAL (
    SELECT amount FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
    LIMIT 1
) recent ON true;

-- Subqueries
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'US'
);

-- Set operations
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

-- GROUPING SETS
SELECT brand, category, SUM(sales)
FROM products
GROUP BY GROUPING SETS ((brand), (category), (brand, category), ());

A LIMIT or OFFSET argument may be any expression that reduces to a value before the first row is read: a constant, a bind parameter, an uncorrelated scalar subquery, or arithmetic over them. One that reads a column of its own query is refused, as PostgreSQL refuses it (argument of LIMIT must not contain variables); inside a LATERAL subquery it may read the outer row.

FETCH FIRST/FETCH NEXT n ROWS ONLY also accepts WITH TIES instead of ONLY, returning every row tied with the last one on the ORDER BY key.

A DISTINCT aggregate (SUM(DISTINCT amount)) inside GROUPING SETS, ROLLUP, or CUBE is refused rather than silently answering the undeduplicated value; aggregate over a SELECT DISTINCT subquery instead.

Grouping or aggregating a NUMERIC column inside GROUPING SETS, ROLLUP or CUBE is narrower than a plain GROUP BY: only COUNT, SUM, AVG, MIN and MAX are supported, only over an integer or text grouping key, and only without HAVING or DISTINCT. Any other combination that touches a NUMERIC column, such as a NUMERIC grouping key, DISTINCT, HAVING, or a statistical aggregate like STDDEV, is refused rather than computed on an approximate path.

INSERT

-- Single row
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com');

-- Multiple rows
INSERT INTO users (id, username, email)
VALUES (2, 'bob', 'bob@example.com'),
       (3, 'charlie', 'charlie@example.com');

-- Insert from select
INSERT INTO archive SELECT * FROM orders WHERE created_at < '2024-01-01';

-- Upsert: insert, or update the row on a conflicting key
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

-- Upsert on a schema-qualified target: a bare column qualifier in
-- SET or WHERE still binds to the table
CREATE TABLE analytics.stock (sku TEXT PRIMARY KEY, quantity BIGINT);
INSERT INTO analytics.stock VALUES ('SKU-1', 5);
INSERT INTO analytics.stock VALUES ('SKU-1', 3)
ON CONFLICT (sku) DO UPDATE SET quantity = stock.quantity + excluded.quantity;

-- Ignore conflicts instead
INSERT INTO users (id, username) VALUES (1, 'alice')
ON CONFLICT DO NOTHING;

-- Return the inserted rows
INSERT INTO users (id, username) VALUES (4, 'diana')
RETURNING id, username;

UPDATE

UPDATE products
SET price = price * 1.1
WHERE category = 'electronics';

-- Update using a join to another table
UPDATE orders o
SET status = 'vip'
FROM customers c
WHERE o.customer_id = c.id AND c.tier = 'gold';

-- Update using a derived table as the source
UPDATE customers
SET tier = 'gold'
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) big
WHERE customers.id = big.customer_id AND big.total > 1000;

-- Update using a CTE as the source
WITH big_spenders AS (
    SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id
)
UPDATE customers
SET tier = 'gold'
FROM big_spenders
WHERE customers.id = big_spenders.customer_id AND big_spenders.total > 1000;

-- Return what changed
UPDATE products SET price = price * 1.1
WHERE category = 'electronics'
RETURNING id, price;

UPDATE ... FROM accepts a derived table or a CTE as its source exactly like a join to another table.

DELETE

DELETE FROM orders WHERE status = 'cancelled';

-- Delete using a join to another table
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.country = 'XX';

-- Delete using a derived table as the source
DELETE FROM orders o
USING (SELECT id FROM customers WHERE country = 'XX') bad
WHERE o.customer_id = bad.id;

-- Return the deleted rows
DELETE FROM orders WHERE status = 'cancelled' RETURNING id;

DELETE ... USING accepts a derived table or a CTE as its source the same way.

MERGE

MERGE INTO ... WHEN NOT MATCHED THEN INSERT works today: a MERGE statement with only a WHEN NOT MATCHED arm runs end to end, and privileges are enforced on both sides (INSERT on the target, SELECT on the source). For example:

CREATE TABLE merge_target (id INT PRIMARY KEY, value TEXT);
CREATE TABLE merge_source (id INT, value TEXT);
INSERT INTO merge_source VALUES (1, 'new-value');

MERGE INTO merge_target t
USING merge_source s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

Adding a WHEN MATCHED THEN UPDATE or WHEN MATCHED THEN DELETE arm is not functional yet: the engine rejects the whole statement with a clear error ("WHEN MATCHED UPDATE/DELETE arms are not functional") rather than silently doing nothing, even though the WHEN NOT MATCHED arm in that same statement would otherwise run fine on its own. The syntax it will take once MATCHED arms are supported:

MERGE INTO inventory AS t
USING shipments AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET quantity = t.quantity + s.quantity
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (s.sku, s.quantity);

For upsert-shaped merges that need the MATCHED/update behavior today, use INSERT ... ON CONFLICT, which is fully supported (the target column needs a primary-key or unique constraint):

CREATE TABLE inventory_upsert (sku TEXT PRIMARY KEY, quantity BIGINT);
INSERT INTO inventory_upsert VALUES ('SKU-1', 5);
INSERT INTO inventory_upsert (sku, quantity) VALUES ('SKU-1', 3), ('SKU-2', 7)
ON CONFLICT (sku) DO UPDATE SET quantity = inventory_upsert.quantity + excluded.quantity;

Row Locking

-- Lock the selected rows for a later update in the same transaction
SELECT * FROM orders WHERE id = 42 FOR UPDATE;

-- Skip rows another transaction has already locked (queue-style processing)
SELECT * FROM jobs WHERE status = 'ready'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

On a single node, and for a table a cluster node keeps to itself, row locking is PostgreSQL's: a second transaction that wants a row another one has locked (with a locking clause, or by changing it) in a conflicting mode waits until that transaction ends, NOWAIT fails at once with 55P03, SKIP LOCKED leaves the row out, a wait longer than lock_timeout fails with 55P03, and a deadlock ends one of the transactions with 40P01.

A row that another transaction changed and committed while a locking read waited for it is checked again, as in PostgreSQL. Under READ COMMITTED the read returns the row as that transaction left it when it still matches the statement's conditions, its select list computed over the new values, and leaves it out, uncounted by LIMIT, when it no longer matches or was deleted. Under REPEATABLE READ and SERIALIZABLE the statement fails with 40001. Under READ COMMITTED, a statement whose FROM holds a subquery, a WITH query, a function, a table the clause does not lock, or a table without a primary key of an integer, numeric(p, s), text, uuid, bytea or boolean type still fails such a row with 40001.

The four strengths conflict as PostgreSQL's do:

Held \ requested	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				conflict
FOR SHARE			conflict	conflict
FOR NO KEY UPDATE		conflict	conflict	conflict
FOR UPDATE	conflict	conflict	conflict	conflict

An UPDATE that leaves every key column as it was takes FOR NO KEY UPDATE; a DELETE and an UPDATE of a primary or unique key take FOR UPDATE. A table named by several clauses (FOR SHARE FOR UPDATE OF orders) is locked at the strongest of them. On a table without a primary key, FOR KEY SHARE holds a row as FOR SHARE does, so an UPDATE of the row also waits for it.

Row locking on distributed tables

A distributed table, one whose rows a cluster spreads over its nodes, locks no rows. FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE read the rows they select; UPDATE and DELETE change theirs without locking them; and no statement waits for another transaction. Each transaction's reads are checked when it commits instead:

• Of two transactions that change the same row, on the same node or on two, whether or not they selected it FOR UPDATE first, the first to commit wins. The other fails at its COMMIT with 40001 (could not serialize access), at every isolation level, READ COMMITTED included, and leaves nothing behind. No update is ever lost; retry the transaction as for any 40001. A row a transaction only selected counts as read: if it changed and committed meanwhile, a transaction that writes anything is refused the same way.
• NOWAIT refuses a row whose writer is committing right now, at once, with 55P03. A row changed by a transaction that has not reached its COMMIT yet is still returned. Under SERIALIZABLE, a row the statement read as it stood before a commit in flight, without taking it, counts as read: if that commit lands, a transaction that writes anything fails at its COMMIT with 40001.
• SKIP LOCKED leaves out the rows whose writer is committing right now, without waiting for it, and a row it left out never fails its own commit. A row whose writer has not reached its COMMIT yet is not left out: two workers can take it, and the second to commit gets 40001.
• SKIP LOCKED and NOWAIT never wait, at every isolation level and outside a transaction block. A commit counts as in flight until the replica serving the read has applied it, so a row whose commit was acknowledged a moment ago can still be left out or refused where PostgreSQL would return it.
• lock_timeout has no effect on these tables, and no row lock takes part in a deadlock.

Where a single node would make the second writer wait and then (under READ COMMITTED) apply its change on top of the first writer's row, a distributed table refuses the second commit instead. See Row locks on distributed tables for the cluster side of this.

COPY (Bulk Import/Export)

-- Import CSV
COPY orders FROM '/path/to/orders.csv' (FORMAT CSV, HEADER);

-- Export CSV
COPY (SELECT * FROM orders WHERE amount > 1000) TO '/path/to/export.csv' (FORMAT CSV);

-- Import/export the PostgreSQL binary wire format
COPY orders FROM '/path/to/orders.bin' (FORMAT BINARY);
COPY (SELECT * FROM orders WHERE amount > 1000) TO '/path/to/export.bin' (FORMAT BINARY);

Streams rows in batches for fast bulk loading. Four formats are supported: TEXT, CSV, TSV, and the PostgreSQL BINARY wire format. FORMAT CSV (and TEXT/TSV) accept HEADER, DELIMITER, NULL, QUOTE, and ESCAPE options; all five are rejected in FORMAT BINARY mode.

COPY ... TO. The rows are read as your own SELECT reads them: as the session's role, so privileges and row-level security apply, and inside the session's transaction, so it sees its own changes and a REPEATABLE READ transaction's snapshot. COPY ... TO STDOUT streams them to the client through PostgreSQL's copy-out protocol in every format, BINARY included, one message per row, as the query produces them. However large the export, only [execution] copy_pipeline_depth buffers of about 256 KiB stand between the query and the client, and a client that reads slowly makes the export wait instead of growing anything. In CSV output a value equal to the NULL marker is quoted, so with the default marker an empty string is written as "" and a NULL as nothing, and a CSV round trip keeps the two apart.

The default format is TEXT, not CSV. Every example above passes FORMAT explicitly; a COPY with no FORMAT option at all uses PostgreSQL's plain TEXT format instead:

-- No FORMAT given: TEXT, tab-delimited, \N for NULL
COPY orders TO '/path/to/export.tsv';

Each format's DELIMITER and NULL marker default independently when you don't set them explicitly:

Format	Default delimiter	Default NULL marker
TEXT (the default format)	tab	\N
CSV	comma	empty string
TSV	tab	empty string

Empty fields and NULL. An empty field becomes NULL only where NULL is legal for that column: a nullable column reads an empty field as NULL, but a NOT NULL column receives the empty string instead, since NULL is the one value it cannot hold. Setting an explicit NULL '<marker>' is additive, not a replacement for this rule: a nullable column's empty field is still read as NULL even when a marker is also set.

What COPY enforces. COPY enforces the same constraints INSERT does: NOT NULL, CHECK, domain constraints, PRIMARY KEY, UNIQUE, and FOREIGN KEY. A violation aborts the load rather than skipping the bad row, with the same SQLSTATE an INSERT would report (23505 for a duplicate key, 23503 for a missing parent row). It also never substitutes a column's DEFAULT for a field the input file actually supplies, even an empty one.

All or nothing. A COPY ... FROM is one statement, however large its input: its rows are written outside the table as they arrive and become visible together when its transaction commits. A bad row at line 50,001, a cancel, a lost client or a crash leaves none of the rows behind, the error names the line in its CONTEXT (COPY orders, line 50001), a concurrent reader sees none of the rows or all of them, and a COPY inside BEGIN is rolled back with the rest of the transaction. The load is bounded by disk, not memory. On a cluster the same holds on every node at once; see COPY into a distributed table.

---

Transaction Control

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Or rollback
BEGIN;
DELETE FROM important_data;
ROLLBACK;

Isolation levels:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;      -- per-statement snapshot
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- transaction-wide snapshot
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;        -- full serializable isolation

Time Inside a Transaction

PostgreSQL has three different notions of "the current time", and ScramDB reads the same three: now(), transaction_timestamp(), CURRENT_TIMESTAMP and CURRENT_DATE freeze at the instant the transaction begins (BEGIN, not its first query) and stay fixed until it commits or rolls back; statement_timestamp() is re-taken at the start of each top-level statement; clock_timestamp() re-reads the wall clock every time it is evaluated.

BEGIN;
SELECT now() = now() AS same_instant;   -- true: one transaction, one instant
COMMIT;

Savepoints

Roll back part of a transaction without aborting all of it:

BEGIN;
INSERT INTO orders (id, amount) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO orders (id, amount) VALUES (2, 200);
ROLLBACK TO SAVEPOINT sp1;   -- undoes only the second insert
COMMIT;

Sharing a Snapshot

pg_export_snapshot() names the snapshot the calling transaction reads at, and another session makes it its own with SET TRANSACTION SNAPSHOT, as in PostgreSQL: from then on both read exactly the same rows, whatever commits meanwhile. Several connections can read one consistent state in parallel this way, for a parallel dump or a fan-out export.

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();   -- for example 00000003-0000001B-1
COMMIT;

The importing session runs SET TRANSACTION SNAPSHOT before any query of its transaction, which must be REPEATABLE READ or SERIALIZABLE, while the exporting transaction is still open:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '<snapshot-id>';
SELECT count(*) FROM orders;   -- what the exporter reads
COMMIT;

An export names the transaction's own snapshot under REPEATABLE READ and SERIALIZABLE, and the statement's under READ COMMITTED. An id lives while the exporting transaction block is open; one exported outside a block names a transaction that ended with its statement, so importing it finds nothing. An import is checked in PostgreSQL's order, with its SQLSTATEs: 25001 after the transaction's first query (or from inside a subtransaction), 0A000 under READ COMMITTED, 22023 for an id that is not well formed, 42704 for one no open transaction exported, then 0A000 for a snapshot of another database, a SERIALIZABLE import of a snapshot that is not, and a read-write SERIALIZABLE import of a read-only transaction's snapshot.

LOCK TABLE

BEGIN;
LOCK TABLE inventory IN SHARE MODE;
-- consistent reads while the lock is held
COMMIT;

-- Other modes, and don't block waiting for the lock
BEGIN;
LOCK TABLE inventory IN ACCESS SHARE MODE;
LOCK TABLE inventory IN ACCESS EXCLUSIVE MODE NOWAIT;
COMMIT;

LOCK TABLE also works cluster-wide on a distributed deployment.

Two-Phase Commit

BEGIN;
INSERT INTO accounts (id, balance) VALUES (2, 500);
PREPARE TRANSACTION 'txn-42';
-- later, from any session
COMMIT PREPARED 'txn-42';

ROLLBACK PREPARED 'name' discards a prepared transaction instead of committing it.

On a cluster's distributed tables a prepared transaction differs from PostgreSQL's in one way: COMMIT PREPARED can fail. Preparing keeps the transaction's changes, and the rows it read, on the node that prepared it, where they survive a restart of that node; nothing is locked while it waits. COMMIT PREPARED then commits it as COMMIT would. If another transaction committed a change to a row it read or wrote after it read it, COMMIT PREPARED fails with 40001 (could not serialize access) and the prepared transaction is rolled back, leaving nothing behind. If the node cannot learn in time whether the commit went through, COMMIT PREPARED fails with 08007 and the transaction may still have committed: check before running it again (see Error codes). Run COMMIT PREPARED and ROLLBACK PREPARED on the node that prepared the transaction.

Right after that node restarts, COMMIT PREPARED first waits for the node to learn again where the transaction's tables are placed in the cluster, for at most [cluster.transactions] commit_answer_timeout (10 seconds by default). If it cannot learn that in time, for example because the rest of the cluster is still down, COMMIT PREPARED fails with 57P03 having changed nothing: the transaction stays prepared, and running COMMIT PREPARED again later commits it.

Session Timeouts

idle_session_timeout closes a session that stays idle outside a transaction for longer than the setting, with FATAL 57P05 (terminating connection due to idle-session timeout), as in PostgreSQL 14 and later. It is a per-session setting, off (0) by default, in milliseconds or with a unit (us, ms, s, min, h, d); a session inside a transaction block is never closed by it. It can also be set for a connection in its startup options (options=-c idle_session_timeout=5min).

SET idle_session_timeout = '15min';
SHOW idle_session_timeout;
RESET idle_session_timeout;

A session idle inside an open transaction past idle_in_transaction_session_timeout is closed (FATAL 25P03), and a transaction open past transaction_timeout is ended (FATAL 25P04). Each session starts at the value of the [resilience] section of the config file, and SET changes it for the session, as in PostgreSQL:

SET idle_in_transaction_session_timeout = '30s';
SET transaction_timeout = '5min';

Every parameter of a connection's startup packet other than user, database, options and replication is a setting of the session, applied the way SET applies it: a driver's TimeZone, DateStyle, client_encoding, extra_float_digits and application_name take effect before its first statement, and a value a setting refuses refuses the connection with that setting's error (FATAL 22023 invalid value for parameter "TimeZone": "...").

max_connections and superuser_reserved_connections are fixed when the server starts ([general] in the config file). Every session can read them with SHOW, current_setting() and pg_settings (context postmaster); SET, RESET and set_config refuse them with 55P02 (parameter "max_connections" cannot be changed without restarting the server), and a connection that sets one in its startup options is refused with FATAL 55P02.

SHOW max_connections;
SELECT name, setting, context FROM pg_settings
WHERE name IN ('max_connections', 'superuser_reserved_connections');

---

Asynchronous Notifications

LISTEN, UNLISTEN and NOTIFY

PostgreSQL's asynchronous notifications: a session LISTENs on a channel, and every NOTIFY on that channel in the same database reaches it, the sending session included.

LISTEN orders_changed;
NOTIFY orders_changed, 'order 42 shipped';
SELECT pg_notify('orders_changed', 'order 43 shipped');
SELECT pg_listening_channels();
SELECT pg_notification_queue_usage();
UNLISTEN orders_changed;
UNLISTEN *;

They follow PostgreSQL's rules:

• Transactional. LISTEN, UNLISTEN and NOTIFY take effect when their transaction commits. A ROLLBACK, a ROLLBACK TO SAVEPOINT set before them, or a failed commit drops them; outside a transaction block a statement is its own transaction.
• Delivered together, in commit order. One transaction's notifications arrive together, in the order they were sent, with an exact repeat of a channel and payload in one transaction sent once. Transactions are delivered in commit order.
• Delivered when idle. A session receives notifications only while it is idle outside a transaction block: right before it reports ready for its next query, or at once when it is already idle, with no query of its own needed. A session inside a transaction block receives them once the block ends.
• Limits. A channel name is an identifier of at most 63 bytes (a longer identifier is truncated with a notice; pg_notify refuses an empty or longer name with 22023), and a payload must be shorter than 8000 bytes (22023 otherwise). PREPARE TRANSACTION after LISTEN, UNLISTEN or NOTIFY in the same transaction is refused with 0A000.
• Bounded. Queued notifications are held from the server's execution memory budget until every listener has received them; a NOTIFY the budget has no room for is refused with 54000. pg_notification_queue_usage() reports the fraction of that budget the queued notifications hold.

pg_notify(channel, payload) is the function form, usable wherever the statement form is not, for example from a routine or a trigger (PERFORM pg_notify(...)) or with bound parameters. As the only output column of a SELECT it sends one notification per row the query returns:

CREATE TABLE notify_jobs (id INT PRIMARY KEY, state TEXT);
INSERT INTO notify_jobs VALUES (1, 'ready'), (2, 'ready'), (3, 'done');
SELECT pg_notify('jobs', id::text) FROM notify_jobs WHERE state = 'ready';
DROP TABLE notify_jobs;

On a cluster a notification committed on any node reaches the listeners on every node, in the one commit order every node shares. If the cluster cannot take a committed transaction's notifications, its COMMIT succeeds (the data is committed) with a warning, 08006, and listeners on other nodes do not receive them.

A session with no client connection, an application that embeds ScramDB as a library, can NOTIFY but not LISTEN (0A000): it has no connection to receive notifications on.

---

Prepared Statements

Parse and plan a statement once, then run it many times with different values:

PREPARE find_customer (INTEGER) AS
    SELECT * FROM customers WHERE id = $1;

EXECUTE find_customer(42);

DEALLOCATE find_customer;

Cursors

Stream a large result set in batches inside a transaction:

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM orders ORDER BY id;
FETCH 100 FROM c;    -- next 100 rows
FETCH 100 FROM c;
FETCH NEXT FROM c;    -- next single row
FETCH ALL FROM c;     -- every remaining row
MOVE 50 FROM c;       -- advance without returning rows
CLOSE c;
COMMIT;

FETCH past the end of the result set returns 0 rows rather than an error; MOVE past the end reports a short count.

A WITH HOLD cursor outlives the transaction that declared it: COMMIT keeps the rows it has not fetched yet, read at the snapshot it was declared under, and the cursor stays open until CLOSE or the end of the session. A ROLLBACK removes the held cursors its transaction declared. Declared outside a transaction block, it is held at once. pg_cursors.is_holdable reports it.

BEGIN;
DECLARE held CURSOR WITH HOLD FOR SELECT g FROM generate_series(1, 5) AS g;
COMMIT;
FETCH 2 FROM held;   -- 1 and 2, after the COMMIT
CLOSE held;

SCROLL cursors read in both directions. Declaring SCROLL materialises the result so the cursor can move backward, and every direction is available: NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n, FORWARD, BACKWARD and ALL, for both FETCH and MOVE. A cursor declared NO SCROLL reads forward only and refuses a backward direction.

BEGIN;
DECLARE c SCROLL CURSOR FOR SELECT * FROM orders ORDER BY id;
FETCH 10 FROM c;
FETCH BACKWARD 5 FROM c;
FETCH ABSOLUTE 1 FROM c;
MOVE RELATIVE 3 FROM c;
CLOSE c;
COMMIT;

The same verbs (DECLARE ... CURSOR, OPEN, FETCH ... INTO, CLOSE) are also available inside a PL/pgSQL function body.

---

Utility

EXPLAIN and EXPLAIN ANALYZE

-- Show the plan the optimizer chose
EXPLAIN SELECT * FROM orders WHERE amount > 1000;

-- Run the query and show real timing and row counts at each step
EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 1000;

EXPLAIN ANALYZE executes the query and reports the actual time and row count at each step, which is the quickest way to find where a slow query spends its time.

VACUUM and CHECKPOINT

-- Reclaim space from deleted and updated rows
VACUUM orders;

-- Full vacuum, parenthesised options, and more than one target table
VACUUM FULL orders;
VACUUM (FULL, VERBOSE) orders;
VACUUM orders, products;

-- Flush a durable checkpoint to disk
CHECKPOINT;

VACUUM also accepts PostgreSQL's parenthesised option list (VACUUM (FULL, ANALYZE) t) alongside the older keyword form (VACUUM FULL ANALYZE t), and a comma-separated list of target tables in one statement.

A CHECKPOINT that cannot complete fails with checkpoint request failed, as in PostgreSQL; the server log records the reason.

Cluster Administration

On a cluster, ALTER CLUSTER DRAIN '<node-name>' hands every shard a node owns to the other members and then removes the node from the cluster; it runs from any node, and against a node that is already gone. SHOW CLUSTER, SHOW SHARDS and SHOW FRESHNESS print the cluster's members, its shard groups and how current each replica is; the same rows are the scram.nodes, scram.shards and scram.freshness tables, which join like any other. See Scaling and Inspecting the Cluster.

ANALYZE

Collect statistics the optimizer uses for planning:

-- Every table in the current database
ANALYZE;

-- One table, either spelling
ANALYZE orders;
ANALYZE TABLE orders;

-- Vacuum and analyze together
VACUUM ANALYZE orders;

A bare ANALYZE covers every table in the current database, matching PostgreSQL - except another session's own temporary tables, which it never touches. VACUUM ANALYZE (bare or named) analyzes the same target set ANALYZE would.

Unlike PostgreSQL, whose ANALYZE returns no rows, ScramDB's ANALYZE returns a result set: one row per table analyzed, with columns table (TEXT), rows_analyzed (BIGINT), and columns (BIGINT).

What ANALYZE collected is visible as in PostgreSQL. pg_class.reltuples is the table's analyzed row count (-1 until its first ANALYZE), and pg_stats has one row per analyzed column: null_frac, avg_width, n_distinct (a count, or minus a fraction of the rows when the distinct values grow with the table), most_common_vals and most_common_freqs, and histogram_bounds, with the values as text. correlation and the element and range statistics are NULL: they are not collected. A role sees the statistics of a column only when it may read that column, and none for a table whose row-level security applies to it.

CREATE TABLE stats_demo (id INT PRIMARY KEY, color TEXT);
INSERT INTO stats_demo VALUES (1, 'red'), (2, 'red'), (3, 'blue');
ANALYZE stats_demo;
SELECT attname, null_frac, n_distinct, most_common_vals
FROM pg_stats WHERE tablename = 'stats_demo';
SELECT reltuples FROM pg_class WHERE relname = 'stats_demo';
DROP TABLE stats_demo;

Monitoring Sessions

pg_stat_activity has one row per client session, as in PostgreSQL: its database, pid, role, application_name, client address and port, when the session, its transaction and its last statement started and when its state last changed, what it is waiting on (wait_event_type and wait_event: Client/ClientRead while idle, Lock/relation behind a LOCK TABLE, Lock/advisory behind an advisory lock), its state (active, idle, idle in transaction, idle in transaction (aborted)) and the text of its last statement, cut at 1024 bytes. A role that is not a superuser, not a member of pg_monitor and not a member of the session's role sees only the identity columns of other roles' sessions, with <insufficient privilege> as their query.

SELECT pid, usename, application_name, state, wait_event_type, wait_event, query
FROM pg_stat_activity;

leader_pid, client_hostname, backend_xid, backend_xmin and query_id are NULL.