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;

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.

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

DROP INDEX

DROP INDEX idx_users_email;

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).

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;

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);

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.

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.

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.

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.

CLONE/TEMPLATE is refused on a clustered coordinator, because it has no deterministic point in time to replicate across nodes. FROM ... AT TIMESTAMP works on a cluster for the same reason it is refused for CLONE: the timestamp gives every node the same fork point.

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 SEQUENCE, CREATE SCHEMA
COPY in every form, including COPY ... FROM STDIN	ALTER INDEX, COMMENT ON
CREATE TABLE, ALTER TABLE, CREATE INDEX, 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), DROP DATABASE, transaction control, SET/SHOW

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';

---

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;

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

-- 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), ());

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;

-- 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';

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

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';

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

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;

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. FORMAT CSV supports HEADER and DELIMITER options; those options are rejected in FORMAT BINARY mode. COPY ... TO STDOUT with FORMAT BINARY is also rejected: binary COPY needs a real file target.

---

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

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;

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.

---

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.

WITH HOLD cursors (surviving past the declaring transaction) are not supported yet: they need cross-transaction snapshot pinning, and the engine rejects the declaration with a clear error rather than silently degrading. Declare WITHOUT HOLD (the default) and fetch within the transaction. The syntax it will take:

BEGIN;
DECLARE c CURSOR WITH HOLD FOR SELECT * FROM orders ORDER BY id;
COMMIT;
FETCH 100 FROM c;   -- still valid after COMMIT
CLOSE c;

SCROLL cursors (backward movement) are not supported yet either: the cursor stream is forward-only, and SCROLL is rejected with a clear error. Declare NO SCROLL (the default) and use forward FETCH/MOVE directions. The syntax it will take:

BEGIN;
DECLARE c SCROLL CURSOR FOR SELECT * FROM orders ORDER BY id;
FETCH 10 FROM c;
FETCH BACKWARD 5 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;

-- Flush a durable checkpoint to disk
CHECKPOINT;