man postgres

Postgres Cheatsheet

PostgreSQL 18 on one page: installing and connecting, psql meta-commands, databases and schemas, tables, types and constraints, queries with joins, CTEs and window functions, writes, upserts, MERGE and COPY, string, date, JSON, array and full-text functions, indexes, EXPLAIN and tuning, sessions and locks, VACUUM, views, functions and triggers, roles and row-level security, backup and restore, configuration and upgrades, replication, extensions, partitioning and what is new in version 18. SQL examples run through psql heredocs, so every block is copyable as-is; outputs appear only where the result follows exactly from the example's own data.

Showing 436 of 436 commands

Install & connect

17 commands

Get a PostgreSQL 18 server running on Linux, macOS or Docker, then reach it with psql using flags, URIs, service files or a password file.

apt install postgresql-18 beginner Install the current major release on Debian or Ubuntu from the official PGDG repository instead of the older distro package.

Example

bash
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-18
pg_lsclusters

Tip: The Debian packaging creates and starts a cluster called 18/main automatically; pg_lsclusters lists every cluster with its port and data directory.

dnf install postgresql18-server beginner Install on Rocky, Alma or RHEL from the PGDG repo, initialise the data directory and enable the service.

Example

bash
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql18-server
sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
sudo systemctl enable --now postgresql-18

Tip: Disabling the built-in module stops dnf from offering the distro's older PostgreSQL in place of the PGDG packages.

brew install postgresql@18 beginner Install on macOS with Homebrew and run it as a background service.

Example

bash
brew install postgresql@18
brew services start postgresql@18
psql postgres
docker run postgres:18 beginner Start a disposable server in a container with a named volume for the data.

Example

bash
docker run -d --name pg18 \
  -e POSTGRES_PASSWORD=change-me \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:18
docker exec -it pg18 psql -U postgres

Tip: From the 18 image on, the volume belongs at /var/lib/postgresql (the data lives in a versioned subfolder), not /var/lib/postgresql/data as with older tags.

sudo -u postgres psql beginner Open psql as the postgres operating-system user, which maps to the postgres superuser through peer authentication.

Example

bash
sudo -u postgres psql
# or switch user first, then start psql
sudo su - postgres
psql

Tip: On a remote box, ssh in as your own user first (ssh admin@db.example.com) and then switch; avoid opening port 5432 to the internet just to administer it.

psql -h -p -U -d beginner Connect to a specific host, port, role and database with individual flags.

Example

bash
psql -h db.example.com -p 5432 -U app -d shop

Tip: Leaving out -d makes psql use a database with the same name as the role.

psql "postgresql://..." beginner Pass every connection detail in one URI, including TLS options as query parameters.

Example

bash
psql "postgresql://app@db.example.com:5432/shop?sslmode=verify-full&sslrootcert=/etc/ssl/certs/db-ca.pem"

Tip: Keep passwords out of URIs that end up in shell history; use ~/.pgpass or PGPASSWORD for automation instead.

~/.pgpass intermediate Store passwords per host, port, database and role so psql and pg_dump never prompt.

Example

bash
echo 'db.example.com:5432:shop:app:S3cret-Pass' >> ~/.pgpass
chmod 600 ~/.pgpass
psql -h db.example.com -U app -d shop

Tip: libpq silently ignores the file when its permissions are wider than 0600. A * matches any value in a field.

PGHOST / PGUSER / PGDATABASE intermediate Set libpq environment variables once so every client tool picks up the same target.

Example

bash
export PGHOST=db.example.com PGPORT=5432 PGUSER=app PGDATABASE=shop
psql -c 'SELECT current_user, current_database();'
~/.pg_service.conf intermediate Name a connection profile once and reuse it from any libpq client with service=NAME.

Example

bash
cat >> ~/.pg_service.conf <<'EOF'
[shop-prod]
host=db.example.com
port=5432
dbname=shop
user=app
sslmode=require
EOF
psql service=shop-prod
psql -c / -f beginner Run one statement or a whole SQL file without opening an interactive session.

Example

bash
psql -d shop -c 'SELECT count(*) FROM orders;'
psql -d shop -v ON_ERROR_STOP=1 -f migrations/001_init.sql

Tip: Without ON_ERROR_STOP=1, psql keeps executing the rest of the file after a failed statement and still exits with status 0.

psql -1 -f intermediate Wrap an entire script in one transaction so it applies completely or not at all.

Example

bash
psql -d shop -1 -v ON_ERROR_STOP=1 -f migrations/002_add_index.sql
psql -At intermediate Get bare values without headers or padding, ideal for capturing results in shell variables.

Example

bash
count=$(psql -d shop -At -c 'SELECT count(*) FROM orders;')
echo "orders: $count"
psql --csv intermediate Print query results as CSV, ready to redirect into a file.

Example

bash
psql -d shop --csv -c 'SELECT id, name, country FROM customers ORDER BY id;' > customers.csv
pg_isready beginner Check whether a server is accepting connections; the exit code makes it handy in scripts and health checks.

Example

bash
pg_isready -h localhost -p 5432
localhost:5432 - accepting connections

Tip: Exit codes: 0 accepting, 1 rejecting (for example during startup), 2 no response, 3 no attempt made.

psql -V / SELECT version() beginner Show the client version, then ask the server which version and build it is running.

Example

bash
psql -V
psql -d shop -c 'SELECT version();'
psql -d shop -At -c 'SHOW server_version_num;'

Tip: server_version_num (for example 180006 for 18.6) is easier to compare in scripts than the version string.

kubectl exec ... psql intermediate Open psql inside a Postgres pod running on Kubernetes.

Example

bash
kubectl exec -it -n databases postgres-0 -- psql -U postgres -d shop

psql meta-commands

34 commands

Backslash commands that psql handles itself: describing objects, formatting output, variables, scripting helpers and editing.

\? and \h beginner List every psql backslash command, or get the full SQL syntax of any statement.

Example

bash
psql -d shop <<'SQL'
\?
\h CREATE INDEX
\h ALTER TABLE
SQL

Tip: \h with no argument prints the list of SQL commands it has help for.

\l / \l+ beginner List databases; the plus form adds size, tablespace and description.

Example

bash
psql -l
psql -d shop -c '\l+'
\c dbname beginner Reconnect to another database (and optionally as another role) inside the same session.

Example

bash
psql -d shop <<'SQL'
\c analytics
\c shop app
SQL

Tip: \c keeps the current host and port unless you pass them: \c dbname user host port.

\conninfo beginner Show who you are connected as, to which database, server and port, and whether TLS is in use.

Example

bash
psql -d shop -c '\conninfo'

Tip: In psql 18 the output became a table with extra details such as protocol version and SSL settings.

\dt / \dt+ beginner List tables in the search path; the plus form adds size, persistence and description.

Example

bash
psql -d shop -c '\dt+'
\dt schema.* / \dt *.* beginner List tables in one schema, or in every schema including the system ones.

Example

bash
psql -d shop -c '\dt sales.*'
psql -d shop -c '\dt *.*'
\d table beginner Describe a table: columns, types, defaults, indexes, constraints, triggers and referencing foreign keys.

Example

bash
psql -d shop -c '\d orders'
\d+ table beginner Same description plus storage, compression, statistics target and column comments.

Example

bash
psql -d shop -c '\d+ orders'
\dn \dv \dm \di \ds \dT beginner List schemas, views, materialized views, indexes, sequences and data types.

Example

bash
psql -d shop <<'SQL'
\dn
\dv
\dm
\di
\ds
\dT
SQL

Tip: Every list command accepts a pattern: \di orders* shows only indexes whose name starts with orders.

\dv *.* beginner List views from every schema, not just those on the search path.

Example

bash
psql -d shop -c '\dv *.*'
\df / \df+ beginner List functions and procedures; the plus form shows language, volatility, owner and source.

Example

bash
psql -d shop -c '\df public.*'
psql -d shop -c '\df+ add_vat'
\sf / \sv intermediate Print the CREATE statement of a function or a view.

Example

bash
psql -d shop <<'SQL'
\sf add_vat
\sv paid_orders
SQL
\ef / \ev intermediate Open a function or view definition in $EDITOR and run the edited version when you save.

Example

bash
# interactive only: start psql, then type the command at the shop=# prompt
psql -d shop
# \ef add_vat
# \ev paid_orders

Tip: Set EDITOR=vim (or nano, code --wait) before starting psql to choose the editor.

\du / \drg beginner List roles with their attributes, and (since 16) the role-membership grants.

Example

bash
psql -d shop <<'SQL'
\du
\drg
SQL
\dp / \ddp intermediate Show table privileges (access control lists) and the default privileges for future objects.

Example

bash
psql -d shop <<'SQL'
\dp orders
\ddp
SQL
\dx / \dx+ beginner List installed extensions; the plus form lists every object an extension created.

Example

bash
psql -d shop <<'SQL'
\dx
\dx+ pg_trgm
SQL
\dconfig intermediate Show server settings that differ from their defaults, or match a pattern.

Example

bash
psql -d shop <<'SQL'
\dconfig
\dconfig *mem*
SQL
\x auto beginner Switch to expanded (one field per line) output, or let psql choose it when rows are too wide.

Example

bash
psql -d shop <<'SQL'
\x auto
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();
SQL
\gx beginner Run the current query once with expanded output, without changing the \x setting.

Example

bash
psql -d shop <<'SQL'
SELECT * FROM pg_settings WHERE name = 'work_mem' \gx
SQL
\timing on beginner Print how long each statement took on the client side.

Example

bash
psql -d shop <<'SQL'
\timing on
SELECT count(*) FROM orders;
SQL
\watch intermediate Re-run the last query on an interval, with an optional run count.

Example

bash
psql -d shop <<'SQL'
SELECT state, count(*) FROM pg_stat_activity GROUP BY state
\watch interval=2 count=5
SQL

Tip: psql 18 adds the WATCH_INTERVAL variable to change the default wait used by a bare \watch.

\set and :var intermediate Define psql variables and interpolate them as values (:'var' adds quotes) or identifiers (:"var").

Example

bash
psql -d shop <<'SQL'
\set cust 2
\set tbl orders
SELECT id, total FROM :"tbl" WHERE customer_id = :cust ORDER BY id;
SQL

Tip: Pass variables from the shell with psql -v cust=2 -f report.sql.

\gset intermediate Store columns of a one-row result in psql variables named after the columns.

Example

bash
psql -d shop <<'SQL'
SELECT count(*) AS n, max(total) AS top FROM orders \gset
\echo :n orders, biggest :top
SQL
5 orders, biggest 240.00
\if / \elif / \endif advanced Branch inside a psql script on a variable, for example a value fetched with \gset.

Example

bash
psql -d shop <<'SQL'
SELECT to_regclass('public.orders') IS NOT NULL AS has_orders \gset
\if :has_orders
  \echo orders table found
\else
  \echo orders table missing
\endif
SQL
\i / \ir beginner Execute a SQL file; \ir resolves the path relative to the file that calls it.

Example

bash
psql -d shop <<'SQL'
\i schema/tables.sql
\ir seed/customers.sql
SQL
\o file intermediate Send query results to a file (or a pipe) until \o is given again.

Example

bash
psql -d shop <<'SQL'
\o /tmp/report.txt
SELECT * FROM orders ORDER BY id;
\o
SQL
\copy beginner Import or export with COPY syntax, but read and write files on the client machine.

Example

bash
psql -d shop <<'SQL'
\copy (SELECT * FROM orders WHERE status = 'paid') TO 'paid_orders.csv' WITH (FORMAT csv, HEADER)
\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER)
SQL

Tip: \copy needs no superuser or pg_read_server_files role, because the client does the file I/O.

\pset intermediate Change output formatting: how NULL looks, the table style, borders and the output format.

Example

bash
psql -d shop <<'SQL'
\pset null '(null)'
\pset border 2
\pset format wrapped
SELECT * FROM customers ORDER BY id;
SQL

Tip: Formats include aligned, unaligned, wrapped, csv, html, asciidoc, latex and troff-ms.

\crosstabview advanced Pivot a three-column result into a grid: rows, columns and cell values.

Example

bash
psql -d shop <<'SQL'
SELECT customer_id, status, count(*)
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2
\crosstabview
SQL
\bind advanced Run a query with $1-style parameters through the extended protocol, as an application driver would.

Example

bash
psql -d shop <<'SQL'
SELECT id, total FROM orders WHERE customer_id = $1 AND total > $2 \bind 2 100 \g
SQL
\errverbose intermediate Repeat the last error with every field: SQLSTATE, detail, hint and source location.

Example

bash
psql -d shop <<'SQL'
SELECT 1/0;
\errverbose
SQL
\echo \! \getenv intermediate Print text, run a shell command, or copy an environment variable into a psql variable.

Example

bash
psql -d shop <<'SQL'
\echo starting report
\! date
\getenv home HOME
\echo :home
SQL
\s and \q beginner Show (or save) the command history, and quit psql.

Example

bash
psql -d shop <<'SQL'
\s /tmp/psql_history.txt
\q
SQL

Tip: Ctrl+D also quits; Ctrl+R searches history like in bash.

~/.psqlrc intermediate Make your favourite settings stick: run on every interactive psql start.

Example

bash
cat >> ~/.psqlrc <<'EOF'
\set QUIET 1
\pset null '(null)'
\x auto
\timing on
\set ON_ERROR_ROLLBACK interactive
\set HISTCONTROL ignoredups
\set PROMPT1 '%n@%/%R%# '
\unset QUIET
EOF

Tip: ON_ERROR_ROLLBACK interactive sets an implicit savepoint per statement, so a typo inside BEGIN no longer aborts the whole transaction.

Databases & schemas

14 commands

Create, change, size and drop databases, and organise objects into schemas with the right search_path.

CREATE DATABASE beginner Create a database owned by an application role.

Example

bash
psql -d postgres <<'SQL'
CREATE DATABASE shop OWNER app;
SQL
CREATE DATABASE
CREATE DATABASE ... LOCALE_PROVIDER advanced Pick encoding and collation explicitly; the builtin provider (17+) gives fast, stable C.UTF-8 sorting.

Example

bash
psql -d postgres <<'SQL'
CREATE DATABASE shop_utf8
  TEMPLATE template0
  ENCODING 'UTF8'
  LOCALE_PROVIDER builtin
  BUILTIN_LOCALE 'C.UTF-8';
SQL
CREATE DATABASE

Tip: Use template0 whenever you change encoding or locale; template1 may already contain data in its own locale.

createdb / dropdb beginner Create or drop a database from the shell without opening psql.

Example

bash
createdb -O app shop
dropdb --if-exists shop_old
DROP DATABASE ... WITH (FORCE) intermediate Drop a database even while clients are connected by terminating their sessions first.

Example

bash
psql -d postgres <<'SQL'
DROP DATABASE IF EXISTS shop_old WITH (FORCE);
SQL

Tip: The shell equivalent is dropdb --force shop_old. You cannot drop the database you are connected to.

ALTER DATABASE intermediate Rename a database, change its owner or set per-database defaults.

Example

bash
psql -d postgres <<'SQL'
ALTER DATABASE shop_staging RENAME TO shop_qa;
ALTER DATABASE shop OWNER TO app;
ALTER DATABASE shop SET timezone = 'UTC';
ALTER DATABASE shop CONNECTION LIMIT 100;
SQL
CREATE DATABASE ... TEMPLATE intermediate Clone a database quickly, for example to get a test copy of staging.

Example

bash
psql -d postgres <<'SQL'
CREATE DATABASE shop_test TEMPLATE shop STRATEGY FILE_COPY;
SQL

Tip: Nobody may be connected to the source database while it is copied.

pg_database_size() beginner List every database with its size on disk, largest first.

Example

bash
psql -d shop <<'SQL'
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
SQL
CREATE SCHEMA beginner Create a namespace for related objects, optionally owned by another role.

Example

bash
psql -d shop <<'SQL'
CREATE SCHEMA IF NOT EXISTS sales AUTHORIZATION app;
SQL
CREATE SCHEMA
SHOW search_path beginner See which schemas unqualified names are looked up in, in order.

Example

bash
psql -d shop <<'SQL'
SHOW search_path;
SQL
   search_path
-----------------
 "$user", public
(1 row)
SET search_path intermediate Change the lookup order for this session, or persist it per role or database.

Example

bash
psql -d shop <<'SQL'
SET search_path TO sales, public;
ALTER ROLE app SET search_path = sales, public;
SQL

Tip: Since PostgreSQL 15 ordinary roles can no longer create objects in public; grant CREATE on a dedicated schema instead.

ALTER TABLE ... SET SCHEMA intermediate Move a table (with its indexes and sequences) into another schema.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE public.invoices SET SCHEMA sales;
SQL
ALTER TABLE
DROP SCHEMA ... CASCADE intermediate Remove a schema together with everything inside it.

Example

bash
psql -d shop <<'SQL'
DROP SCHEMA IF EXISTS scratch CASCADE;
SQL

Tip: Run it once without CASCADE first; the error lists every object that would be removed.

current_database() / current_user beginner Check where you are and as whom, from SQL rather than psql.

Example

bash
psql -d shop <<'SQL'
SELECT current_database(), current_user, session_user, current_schema();
SQL
COMMENT ON DATABASE beginner Attach a description that \l+ displays.

Example

bash
psql -d shop <<'SQL'
COMMENT ON DATABASE shop IS 'Online store, production';
SQL
COMMENT

Tables & data types

20 commands

Create and reshape tables, choose sensible column types, and work with identity columns, sequences, enums, domains and generated columns.

CREATE TABLE beginner Create a table with an identity primary key, typed columns and defaults.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE IF NOT EXISTS products (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        text NOT NULL UNIQUE,
  name       text NOT NULL,
  price      numeric(10,2) NOT NULL CHECK (price >= 0),
  tags       text[] NOT NULL DEFAULT '{}',
  attrs      jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);
SQL
CREATE TABLE

Tip: Prefer text over varchar(n), numeric for money, and timestamptz over timestamp for points in time.

serial vs IDENTITY beginner Both auto-number a column; IDENTITY is the SQL-standard form and keeps the sequence tied to the column.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE legacy_ids (id serial PRIMARY KEY, note text);
CREATE TABLE modern_ids (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text);
SQL
CREATE TABLE
CREATE TABLE

Tip: GENERATED ALWAYS rejects manual ids unless you write OVERRIDING SYSTEM VALUE; BY DEFAULT accepts them.

Common column types beginner One table that shows the types you reach for most often.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE type_tour (
  qty        integer,
  big_count  bigint,
  price      numeric(12,2),
  ratio      double precision,
  active     boolean,
  born_on    date,
  happened   timestamptz,
  duration   interval,
  doc        jsonb,
  labels     text[],
  ref        uuid,
  client_ip  inet,
  payload    bytea
);
SQL
CREATE TABLE
ALTER TABLE ... ADD COLUMN beginner Add a column; with a constant default this is instant even on large tables.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE customers ADD COLUMN IF NOT EXISTS phone text;
ALTER TABLE customers ADD COLUMN vip boolean NOT NULL DEFAULT false;
SQL
ALTER TABLE
ALTER TABLE
ALTER TABLE ... DROP COLUMN beginner Remove a column (and, with CASCADE, views that depend on it).

Example

bash
psql -d shop <<'SQL'
ALTER TABLE customers DROP COLUMN IF EXISTS phone;
SQL
ALTER TABLE
RENAME COLUMN / RENAME TO beginner Rename a column or the table itself.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE customers RENAME COLUMN country TO country_code;
ALTER TABLE customers RENAME TO clients;
SQL
ALTER TABLE
ALTER TABLE
ALTER COLUMN ... TYPE ... USING intermediate Change a column's type, converting existing values with an expression.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders ALTER COLUMN total TYPE numeric(12,2);
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz USING created_at AT TIME ZONE 'UTC';
SQL

Tip: Most type changes rewrite the whole table under an ACCESS EXCLUSIVE lock; schedule them for quiet hours on big tables.

SET / DROP DEFAULT, SET / DROP NOT NULL beginner Change a column's default value or whether it may be NULL.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;
SQL
CREATE TYPE ... AS ENUM intermediate Define a fixed list of allowed labels, and extend it later.

Example

bash
psql -d shop <<'SQL'
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'refunded');
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'shipped' AFTER 'paid';
SQL
CREATE TYPE
ALTER TYPE

Tip: Labels can be added and renamed but not removed; use a lookup table or a CHECK constraint if the list changes often.

CREATE DOMAIN intermediate Create a reusable type with built-in validation.

Example

bash
psql -d shop <<'SQL'
CREATE DOMAIN email_address AS text
  CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');
CREATE TABLE subscribers (id int PRIMARY KEY, email email_address NOT NULL);
SQL
CREATE DOMAIN
CREATE TABLE
GENERATED ALWAYS AS ... STORED intermediate Keep a column computed from others in the same row, written to disk on insert and update.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE people (
  first_name text,
  last_name  text,
  full_name  text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);
SQL
CREATE TABLE

Tip: PostgreSQL 18 makes VIRTUAL (computed on read) the default when you leave out STORED; only STORED columns can be indexed.

CREATE TABLE ... (LIKE ...) intermediate Copy another table's column layout, optionally with defaults, constraints and indexes.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
SQL
CREATE TABLE
CREATE TABLE AS beginner Create and fill a table from a query in one step.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE paid_orders_2026 AS
SELECT * FROM orders WHERE status = 'paid' AND ordered_on >= '2026-01-01';
SQL
SELECT 3

Tip: With the sample data, three paid orders fall in 2026, which is what the SELECT 3 tag reports.

CREATE TEMP / UNLOGGED TABLE intermediate Temporary tables vanish at session (or transaction) end; unlogged tables skip WAL for speed but are emptied after a crash.

Example

bash
psql -d shop <<'SQL'
CREATE TEMP TABLE tmp_ids (id int) ON COMMIT DROP;
CREATE UNLOGGED TABLE import_staging (line text);
SQL

Tip: Unlogged tables are not copied to streaming replicas.

TRUNCATE beginner Empty tables instantly; optionally reset their identity counters and cascade to referencing tables.

Example

bash
psql -d shop <<'SQL'
TRUNCATE TABLE import_staging;
TRUNCATE orders, customers RESTART IDENTITY CASCADE;
SQL

Tip: TRUNCATE takes an ACCESS EXCLUSIVE lock but is transactional: ROLLBACK brings the rows back.

DROP TABLE beginner Delete a table and its data; CASCADE also removes dependent views and foreign keys.

Example

bash
psql -d shop <<'SQL'
DROP TABLE IF EXISTS orders_archive;
SQL
DROP TABLE
COMMENT ON TABLE / COLUMN beginner Document tables and columns inside the database; \d+ shows the text.

Example

bash
psql -d shop <<'SQL'
COMMENT ON TABLE orders IS 'One row per checkout';
COMMENT ON COLUMN orders.total IS 'Gross amount in the store currency';
SQL
COMMENT
COMMENT
Quoted identifiers intermediate Double quotes keep case, spaces and symbols in names; without them, names fold to lower case.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE "Order Lines" ("Line No" int, "qty&unit" text);
SELECT "Line No" FROM "Order Lines";
SQL

Tip: Once a name needs quotes it needs them forever; stick to lower_snake_case to avoid that.

Sequences intermediate Create, read and move sequences; pg_get_serial_sequence finds the one behind a column.

Example

bash
psql -d shop <<'SQL'
CREATE SEQUENCE invoice_no START 1000;
SELECT nextval('invoice_no'), currval('invoice_no');
SELECT setval(pg_get_serial_sequence('products', 'id'),
              (SELECT max(id) FROM products));
SQL

Tip: After bulk-loading rows with explicit ids, setval stops later inserts from colliding with them.

Estimated row count intermediate Read the planner's row estimate instead of running count(*) on a huge table.

Example

bash
psql -d shop <<'SQL'
SELECT reltuples::bigint AS estimated_rows
FROM pg_class
WHERE oid = 'public.orders'::regclass;
SQL

Tip: The estimate is refreshed by VACUUM and ANALYZE; it is -1 for a table that has never been analyzed.

Constraints

11 commands

Keep data valid in the database itself: keys, foreign keys, checks, uniqueness and exclusion, including ways to add them without long locks.

PRIMARY KEY beginner Declare a single or composite primary key.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE order_items (
  order_id int,
  line_no  int,
  sku      text NOT NULL,
  qty      int NOT NULL,
  PRIMARY KEY (order_id, line_no)
);
SQL
CREATE TABLE
FOREIGN KEY ... ON DELETE beginner Link rows to a parent table and decide what happens when the parent is deleted.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;
SQL
ALTER TABLE

Tip: Postgres does not index the referencing column for you; add CREATE INDEX ON order_items (order_id) to keep deletes and joins fast.

UNIQUE beginner Reject duplicates across one or more columns.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);
SQL
UNIQUE NULLS NOT DISTINCT intermediate Treat NULLs as equal so only one NULL is allowed (15+).

Example

bash
psql -d shop <<'SQL'
CREATE TABLE coupons (
  code       text,
  account_id int,
  UNIQUE NULLS NOT DISTINCT (code, account_id)
);
SQL
CREATE TABLE
CHECK beginner Validate each row against a boolean expression.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total > 0);
INSERT INTO orders VALUES (99, 1, 0, 'paid', '2026-11-09');
SQL

Tip: The second statement fails with: new row for relation "orders" violates check constraint "orders_total_positive".

NOT VALID + VALIDATE CONSTRAINT advanced Add a check or foreign key without scanning existing rows, then validate later under a weaker lock.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
SQL
ALTER TABLE
ALTER TABLE

Tip: New rows are checked immediately; VALIDATE only scans old rows and does not block normal reads and writes.

ADD ... UNIQUE USING INDEX advanced Build a unique index concurrently first, then promote it to a constraint almost instantly.

Example

bash
psql -d shop <<'SQL'
CREATE UNIQUE INDEX CONCURRENTLY products_sku_idx ON products (sku);
ALTER TABLE products ADD CONSTRAINT products_sku_key UNIQUE USING INDEX products_sku_idx;
SQL
EXCLUDE USING gist advanced Forbid overlapping ranges, for example two bookings of the same room at the same time.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
SQL
CREATE EXTENSION
CREATE TABLE
DEFERRABLE INITIALLY DEFERRED advanced Check a constraint at COMMIT instead of after each statement, useful for circular references or swaps.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE order_items ALTER CONSTRAINT order_items_order_fk DEFERRABLE INITIALLY IMMEDIATE;
BEGIN;
SET CONSTRAINTS order_items_order_fk DEFERRED;
-- insert children before parents here
COMMIT;
SQL
DROP CONSTRAINT beginner Remove a constraint by name.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_total_positive;
SQL
ALTER TABLE
List constraints intermediate Show every constraint on a table with its type and full definition.

Example

bash
psql -d shop <<'SQL'
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass
ORDER BY conname;
SQL

Tip: contype: p primary key, f foreign key, u unique, c check, x exclusion, n not null (18).

Select & filter

14 commands

Load the small sample dataset used in the next sections, then filter, sort, page and de-duplicate rows. Outputs below come from that data.

Sample data for this sheet beginner Two small tables (4 customers, 5 orders) that the query examples and their outputs are based on.

Example

bash
psql -d shop <<'SQL'
DROP TABLE IF EXISTS orders, customers CASCADE;
CREATE TABLE customers (
  id      int PRIMARY KEY,
  name    text NOT NULL,
  email   text UNIQUE,
  country text
);
CREATE TABLE orders (
  id          int PRIMARY KEY,
  customer_id int REFERENCES customers (id),
  total       numeric(10,2) NOT NULL,
  status      text NOT NULL,
  ordered_on  date NOT NULL
);
INSERT INTO customers VALUES
  (1, 'Ana Silva',  'ana@example.com',  'BR'),
  (2, 'Ben Carter', 'ben@example.com',  'US'),
  (3, 'Chen Wei',   'chen@example.com', 'CN'),
  (4, 'Dana Kim',   NULL,               'US');
INSERT INTO orders VALUES
  (1, 1, 120.00, 'paid',     '2026-10-01'),
  (2, 1,  35.50, 'paid',     '2026-10-15'),
  (3, 2,  89.90, 'pending',  '2026-10-20'),
  (4, 3,  15.00, 'refunded', '2026-11-02'),
  (5, 2, 240.00, 'paid',     '2026-11-05');
SQL

Tip: Run it in a throwaway database (createdb shop) because it drops and recreates customers and orders.

SELECT ... WHERE ... ORDER BY beginner Pick columns, filter rows and sort the result.

Example

bash
psql -d shop <<'SQL'
SELECT id, total, status
FROM orders
WHERE total > 50
ORDER BY total DESC;
SQL
 id | total  | status
----+--------+---------
  5 | 240.00 | paid
  1 | 120.00 | paid
  3 |  89.90 | pending
(3 rows)
LIMIT / OFFSET beginner Return only the first rows of a sorted result, for example the latest orders.

Example

bash
psql -d shop <<'SQL'
SELECT id, ordered_on
FROM orders
ORDER BY ordered_on DESC
LIMIT 2;
SQL
 id | ordered_on
----+------------
  5 | 2026-11-05
  4 | 2026-11-02
(2 rows)

Tip: Large OFFSET values still read and discard every skipped row; page with WHERE id > :last_id for big tables.

FETCH FIRST ... WITH TIES intermediate Take the top N rows plus any rows that tie with the last one.

Example

bash
psql -d shop <<'SQL'
SELECT id, status
FROM orders
ORDER BY status
FETCH FIRST 1 ROWS WITH TIES;
SQL

Tip: With the sample data this returns all three paid orders, because they share the first status value.

BETWEEN / IN / IS NULL beginner Filter on ranges, lists and missing values.

Example

bash
psql -d shop <<'SQL'
SELECT id FROM orders WHERE ordered_on BETWEEN '2026-10-10' AND '2026-10-31' ORDER BY id;
SQL
 id
----
  2
  3
(2 rows)

Tip: Use IS NULL / IS NOT NULL; email = NULL is never true. BETWEEN includes both ends.

= ANY (array) intermediate Match against a list passed as one array, which also works well as a single bind parameter.

Example

bash
psql -d shop <<'SQL'
SELECT id, status
FROM orders
WHERE status = ANY (ARRAY['pending', 'refunded'])
ORDER BY id;
SQL
 id |  status
----+----------
  3 | pending
  4 | refunded
(2 rows)
LIKE / ILIKE beginner Match text patterns: % is any run of characters, _ is exactly one; ILIKE ignores case.

Example

bash
psql -d shop <<'SQL'
SELECT name FROM customers WHERE name ILIKE '%an%' ORDER BY name;
SQL
   name
-----------
 Ana Silva
 Dana Kim
(2 rows)
~ and ~* intermediate Filter with POSIX regular expressions (~* is case-insensitive, !~ negates).

Example

bash
psql -d shop <<'SQL'
SELECT email FROM customers WHERE email ~ '^[a-c]' ORDER BY email;
SQL
      email
------------------
 ana@example.com
 ben@example.com
 chen@example.com
(3 rows)
COALESCE / NULLIF beginner Replace NULLs with a fallback, or turn a sentinel value into NULL.

Example

bash
psql -d shop <<'SQL'
SELECT name, coalesce(email, 'no email') AS email
FROM customers
ORDER BY id;
SQL
    name    |      email
------------+------------------
 Ana Silva  | ana@example.com
 Ben Carter | ben@example.com
 Chen Wei   | chen@example.com
 Dana Kim   | no email
(4 rows)

Tip: NULLIF(x, 0) is the classic guard against division by zero: total / NULLIF(qty, 0).

CASE beginner Derive a value with if/else logic inside a query.

Example

bash
psql -d shop <<'SQL'
SELECT id, total,
       CASE WHEN total >= 100 THEN 'large'
            WHEN total >= 50  THEN 'medium'
            ELSE 'small' END AS size
FROM orders
ORDER BY id;
SQL
 id | total  |  size
----+--------+--------
  1 | 120.00 | large
  2 |  35.50 | small
  3 |  89.90 | medium
  4 |  15.00 | small
  5 | 240.00 | large
(5 rows)
DISTINCT / DISTINCT ON intermediate Remove duplicate rows, or keep only the first row per group, such as each customer's latest order.

Example

bash
psql -d shop <<'SQL'
SELECT DISTINCT ON (customer_id) customer_id, id, ordered_on
FROM orders
ORDER BY customer_id, ordered_on DESC;
SQL
 customer_id | id | ordered_on
-------------+----+------------
           1 |  2 | 2026-10-15
           2 |  5 | 2026-11-05
           3 |  4 | 2026-11-02
(3 rows)

Tip: The ORDER BY must start with the DISTINCT ON columns; the next sort key decides which row wins.

ORDER BY ... NULLS LAST beginner Control where NULLs land when sorting.

Example

bash
psql -d shop <<'SQL'
SELECT name, email FROM customers ORDER BY email DESC NULLS LAST;
SQL
    name    |      email
------------+------------------
 Chen Wei   | chen@example.com
 Ben Carter | ben@example.com
 Ana Silva  | ana@example.com
 Dana Kim   |
(4 rows)

Tip: By default NULLs sort as if larger than every value: last in ASC, first in DESC.

TABLESAMPLE advanced Read a random sample of a large table's pages instead of every row.

Example

bash
psql -d shop <<'SQL'
SELECT * FROM orders TABLESAMPLE SYSTEM (1);
SQL

Tip: SYSTEM samples whole pages (fast, clumpy); BERNOULLI samples individual rows (slower, more even).

Dollar quoting and E'' strings intermediate Write text containing quotes without escaping, or use backslash escapes such as newlines.

Example

bash
psql -d shop <<'SQL'
SELECT $$it's here$$ AS quoted, E'line 1\nline 2' AS escaped;
SQL

Tip: Dollar quotes ($$ or $tag$) are also how function bodies are usually written.

Joins, subqueries & CTEs

15 commands

Combine tables with joins, filter with subqueries, and structure longer queries with common table expressions, including recursive ones.

INNER JOIN beginner Keep only rows that have a match on both sides.

Example

bash
psql -d shop <<'SQL'
SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.id;
SQL
    name    | order_id | total
------------+----------+--------
 Ana Silva  |        1 | 120.00
 Ana Silva  |        2 |  35.50
 Ben Carter |        5 | 240.00
(3 rows)
LEFT JOIN beginner Keep every row from the left table, with NULLs where the right side has no match.

Example

bash
psql -d shop <<'SQL'
SELECT c.name, count(o.id) AS orders, coalesce(sum(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;
SQL
    name    | orders | spent
------------+--------+--------
 Ben Carter |      2 | 329.90
 Ana Silva  |      2 | 155.50
 Chen Wei   |      1 |  15.00
 Dana Kim   |      0 |      0
(4 rows)

Tip: count(o.id) counts only matched rows, so customers without orders show 0 instead of 1.

RIGHT / FULL OUTER JOIN intermediate Keep unmatched rows from the right side, or from both sides.

Example

bash
psql -d shop <<'SQL'
SELECT c.name, o.id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL OR o.id IS NULL;
SQL

Tip: This pattern lists orphans on either side, which is handy when checking data imports.

JOIN ... USING beginner Shorten the join condition when both columns have the same name.

Example

bash
psql -d shop <<'SQL'
SELECT order_id, line_no, sku, carrier
FROM order_items
JOIN shipments USING (order_id);
SQL

Tip: The shared column appears once in SELECT * output; NATURAL JOIN goes further and joins on every same-named column, which breaks silently when someone adds a column.

CROSS JOIN intermediate Pair every row of one set with every row of another.

Example

bash
psql -d shop <<'SQL'
SELECT s.size, c.color
FROM (VALUES ('S'), ('M')) AS s(size)
CROSS JOIN (VALUES ('red'), ('blue')) AS c(color)
ORDER BY 1, 2;
SQL
 size | color
------+-------
 M    | blue
 M    | red
 S    | blue
 S    | red
(4 rows)
Self join intermediate Join a table to itself, for example to find pairs of customers in the same country.

Example

bash
psql -d shop <<'SQL'
SELECT a.name, b.name AS same_country
FROM customers a
JOIN customers b ON b.country = a.country AND b.id > a.id;
SQL
    name    | same_country
------------+--------------
 Ben Carter | Dana Kim
(1 row)
LATERAL advanced Run a subquery once per outer row, the standard way to get the top N rows per group.

Example

bash
psql -d shop <<'SQL'
SELECT c.name, last.id AS last_order, last.ordered_on
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, ordered_on
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY ordered_on DESC
  LIMIT 1
) AS last
ORDER BY c.id;
SQL
    name    | last_order | ordered_on
------------+------------+------------
 Ana Silva  |          2 | 2026-10-15
 Ben Carter |          5 | 2026-11-05
 Chen Wei   |          4 | 2026-11-02
(3 rows)

Tip: Use LEFT JOIN LATERAL (...) ON true to keep customers that have no orders.

IN (subquery) beginner Filter by values returned from another query.

Example

bash
psql -d shop <<'SQL'
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100)
ORDER BY name;
SQL
    name
------------
 Ana Silva
 Ben Carter
(2 rows)
EXISTS / NOT EXISTS intermediate Test whether related rows exist; NOT EXISTS is the safe anti-join.

Example

bash
psql -d shop <<'SQL'
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
SQL
   name
----------
 Dana Kim
(1 row)

Tip: Prefer NOT EXISTS over NOT IN: a single NULL in the subquery makes NOT IN return no rows at all.

Scalar subquery intermediate Use a one-value subquery as a column.

Example

bash
psql -d shop <<'SQL'
SELECT id, total, (SELECT max(total) FROM orders) AS biggest
FROM orders
WHERE id <= 2
ORDER BY id;
SQL
 id | total  | biggest
----+--------+---------
  1 | 120.00 |  240.00
  2 |  35.50 |  240.00
(2 rows)
UNION / INTERSECT / EXCEPT beginner Combine result sets; UNION removes duplicates, UNION ALL keeps them.

Example

bash
psql -d shop <<'SQL'
SELECT country FROM customers
UNION
SELECT 'DE'
ORDER BY country;
SQL
 country
---------
 BR
 CN
 DE
 US
(4 rows)
WITH (CTE) intermediate Name an intermediate result and query it like a table.

Example

bash
psql -d shop <<'SQL'
WITH monthly AS (
  SELECT date_trunc('month', ordered_on)::date AS month, sum(total) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT month, revenue FROM monthly ORDER BY month;
SQL
   month    | revenue
------------+---------
 2026-10-01 |  155.50
 2026-11-01 |  240.00
(2 rows)
WITH RECURSIVE advanced Walk hierarchies or generate sequences by repeatedly joining a CTE to itself.

Example

bash
psql -d shop <<'SQL'
WITH RECURSIVE n(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT sum(i) FROM n;
SQL
 sum
-----
  15
(1 row)
Recursive tree walk advanced List an org chart (or category tree) with each row's depth and path.

Example

bash
psql -d shop <<'SQL'
WITH RECURSIVE tree AS (
  SELECT id, manager_id, name, 1 AS depth, name::text AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, t.depth + 1, t.path || ' > ' || e.name
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT depth, path FROM tree ORDER BY path;
SQL

Tip: Add CYCLE id SET is_cycle USING route after the CTE to stop safely if the data contains a loop.

Data-modifying CTE advanced Move rows between tables in one atomic statement.

Example

bash
psql -d shop <<'SQL'
WITH moved AS (
  DELETE FROM orders
  WHERE status = 'refunded'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
SQL

Aggregates & window functions

17 commands

Summarise rows with GROUP BY, FILTER and ROLLUP, and compute running totals, rankings and comparisons with window functions. Outputs use the sample data.

count / sum / avg / min / max beginner The core aggregates over the whole table.

Example

bash
psql -d shop <<'SQL'
SELECT count(*) AS orders, sum(total) AS revenue, round(avg(total), 2) AS avg_order,
       min(total), max(total)
FROM orders;
SQL
 orders | revenue | avg_order |  min  |  max
--------+---------+-----------+-------+--------
      5 |  500.40 |    100.08 | 15.00 | 240.00
(1 row)
GROUP BY ... HAVING beginner Aggregate per group, then keep only groups that pass a condition.

Example

bash
psql -d shop <<'SQL'
SELECT customer_id, count(*) AS orders, sum(total) AS spent
FROM orders
GROUP BY customer_id
HAVING count(*) > 1
ORDER BY spent DESC;
SQL
 customer_id | orders | spent
-------------+--------+--------
           2 |      2 | 329.90
           1 |      2 | 155.50
(2 rows)

Tip: WHERE filters rows before grouping; HAVING filters the groups afterwards.

count(DISTINCT ...) beginner Count unique values.

Example

bash
psql -d shop <<'SQL'
SELECT count(DISTINCT customer_id) FROM orders;
SQL
 count
-------
     3
(1 row)
FILTER (WHERE ...) intermediate Compute several conditional aggregates in one pass instead of one query each.

Example

bash
psql -d shop <<'SQL'
SELECT count(*) AS all_orders,
       count(*) FILTER (WHERE status = 'paid') AS paid,
       sum(total) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders;
SQL
 all_orders | paid | paid_revenue
------------+------+--------------
          5 |    3 |       395.50
(1 row)
string_agg / array_agg intermediate Collapse a group's values into a delimited string or an array, in a chosen order.

Example

bash
psql -d shop <<'SQL'
SELECT customer_id, string_agg(id::text, ',' ORDER BY id) AS order_ids
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
SQL
 customer_id | order_ids
-------------+-----------
           1 | 1,2
           2 | 3,5
           3 | 4
(3 rows)
array_agg intermediate Gather each group's values into an array.

Example

bash
psql -d shop <<'SQL'
SELECT country, array_agg(name ORDER BY name) AS names
FROM customers
GROUP BY country
ORDER BY country;
SQL
 country |           names
---------+---------------------------
 BR      | {"Ana Silva"}
 CN      | {"Chen Wei"}
 US      | {"Ben Carter","Dana Kim"}
(3 rows)
ROLLUP intermediate Add subtotal and grand-total rows to a grouped result.

Example

bash
psql -d shop <<'SQL'
SELECT status, count(*) AS orders, sum(total) AS revenue
FROM orders
GROUP BY ROLLUP (status)
ORDER BY status NULLS LAST;
SQL
  status  | orders | revenue
----------+--------+---------
 paid     |      3 |  395.50
 pending  |      1 |   89.90
 refunded |      1 |   15.00
          |      5 |  500.40
(4 rows)

Tip: The row with an empty status is the grand total; grouping(status) = 1 tells it apart from a real NULL.

GROUPING SETS / CUBE advanced Produce several groupings in one query: per status, per customer and overall.

Example

bash
psql -d shop <<'SQL'
SELECT status, customer_id, sum(total)
FROM orders
GROUP BY GROUPING SETS ((status), (customer_id), ());
SQL
percentile_cont (median) intermediate Compute a median or any percentile with an ordered-set aggregate.

Example

bash
psql -d shop <<'SQL'
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY total) AS median
FROM orders;
SQL
 median
--------
   89.9
(1 row)

Tip: percentile_disc returns an actual value from the set; mode() WITHIN GROUP (ORDER BY x) returns the most frequent one.

bool_and / bool_or intermediate Check whether a condition holds for all rows or for any row in a group.

Example

bash
psql -d shop <<'SQL'
SELECT bool_or(status = 'refunded') AS any_refund,
       bool_and(total > 10) AS all_over_10
FROM orders;
SQL
 any_refund | all_over_10
------------+-------------
 t          | t
(1 row)
row_number() OVER (PARTITION BY ...) intermediate Number rows within each group, for example orders per customer from largest to smallest.

Example

bash
psql -d shop <<'SQL'
SELECT customer_id, id, total,
       row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM orders
ORDER BY customer_id, rn;
SQL
 customer_id | id | total  | rn
-------------+----+--------+----
           1 |  1 | 120.00 |  1
           1 |  2 |  35.50 |  2
           2 |  5 | 240.00 |  1
           2 |  3 |  89.90 |  2
           3 |  4 |  15.00 |  1
(5 rows)

Tip: Wrap it in a subquery and filter WHERE rn <= 3 to get the top 3 per group.

rank / dense_rank / ntile intermediate Rank rows with gaps after ties (rank), without gaps (dense_rank), or split them into buckets (ntile).

Example

bash
psql -d shop <<'SQL'
SELECT id, total,
       rank()       OVER (ORDER BY total DESC) AS rnk,
       dense_rank() OVER (ORDER BY total DESC) AS dense,
       ntile(2)     OVER (ORDER BY total DESC) AS half
FROM orders;
SQL
Running total intermediate Accumulate a sum in order with an ORDER BY inside OVER().

Example

bash
psql -d shop <<'SQL'
SELECT id, ordered_on, total,
       sum(total) OVER (ORDER BY ordered_on) AS running
FROM orders
ORDER BY ordered_on;
SQL
 id | ordered_on | total  | running
----+------------+--------+---------
  1 | 2026-10-01 | 120.00 |  120.00
  2 | 2026-10-15 |  35.50 |  155.50
  3 | 2026-10-20 |  89.90 |  245.40
  4 | 2026-11-02 |  15.00 |  260.40
  5 | 2026-11-05 | 240.00 |  500.40
(5 rows)
lag / lead intermediate Compare each row with the previous or next one, for example days between orders.

Example

bash
psql -d shop <<'SQL'
SELECT id, ordered_on,
       ordered_on - lag(ordered_on) OVER (ORDER BY ordered_on) AS days_since_prev
FROM orders
ORDER BY ordered_on;
SQL
 id | ordered_on | days_since_prev
----+------------+-----------------
  1 | 2026-10-01 |
  2 | 2026-10-15 |              14
  3 | 2026-10-20 |               5
  4 | 2026-11-02 |              13
  5 | 2026-11-05 |               3
(5 rows)
Share of total intermediate Divide by a window sum over the whole result to get percentages.

Example

bash
psql -d shop <<'SQL'
SELECT id, total,
       round(100.0 * total / sum(total) OVER (), 1) AS pct
FROM orders
ORDER BY id;
SQL
 id | total  | pct
----+--------+------
  1 | 120.00 | 24.0
  2 |  35.50 |  7.1
  3 |  89.90 | 18.0
  4 |  15.00 |  3.0
  5 | 240.00 | 48.0
(5 rows)
Moving average (ROWS frame) advanced Average over a sliding window of rows: here the current order and the one before it.

Example

bash
psql -d shop <<'SQL'
SELECT id, total,
       round(avg(total) OVER (ORDER BY ordered_on
                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 2) AS moving_avg
FROM orders
ORDER BY ordered_on;
SQL
 id | total  | moving_avg
----+--------+------------
  1 | 120.00 |     120.00
  2 |  35.50 |      77.75
  3 |  89.90 |      62.70
  4 |  15.00 |      52.45
  5 | 240.00 |     127.50
(5 rows)

Tip: Use RANGE BETWEEN interval '7 days' PRECEDING AND CURRENT ROW for a time-based window instead of a row count.

first_value / WINDOW clause advanced Name a window once and reuse it across several functions.

Example

bash
psql -d shop <<'SQL'
SELECT id, customer_id, total,
       first_value(total) OVER w AS first_order_total,
       count(*)           OVER w AS orders_so_far
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY ordered_on)
ORDER BY customer_id, ordered_on;
SQL

Insert, update, delete & COPY

17 commands

Write data: inserts, upserts, updates and deletes that return what they changed, MERGE, and bulk loading with COPY. Tags shown assume freshly loaded sample data.

INSERT (multi-row) beginner Insert several rows in one statement.

Example

bash
psql -d shop <<'SQL'
INSERT INTO customers (id, name, email, country) VALUES
  (5, 'Eva Rossi', 'eva@example.com',  'IT'),
  (6, 'Femi Ade',  'femi@example.com', 'NG');
SQL
INSERT 0 2

Tip: One multi-row INSERT is far faster than many single-row ones; for thousands of rows use COPY.

INSERT ... RETURNING beginner Get generated or final values back from the insert without a second query.

Example

bash
psql -d shop <<'SQL'
INSERT INTO customers (id, name, email, country)
VALUES (7, 'Gus Lind', 'gus@example.com', 'SE')
RETURNING id, name;
SQL
 id |   name
----+----------
  7 | Gus Lind
(1 row)

INSERT 0 1
INSERT ... SELECT beginner Copy rows produced by a query into a table.

Example

bash
psql -d shop <<'SQL'
INSERT INTO orders_archive
SELECT * FROM orders WHERE ordered_on < '2026-11-01';
SQL
INSERT 0 3
INSERT ... DEFAULT VALUES / OVERRIDING intermediate Insert a row made only of defaults, or force an explicit value into a GENERATED ALWAYS identity.

Example

bash
psql -d shop <<'SQL'
INSERT INTO audit_ping DEFAULT VALUES;
INSERT INTO products (id, sku, name, price)
OVERRIDING SYSTEM VALUE VALUES (1000, 'IMP-1', 'Imported item', 9.90);
SQL
ON CONFLICT DO NOTHING intermediate Skip rows that would violate a unique constraint instead of failing.

Example

bash
psql -d shop <<'SQL'
INSERT INTO customers (id, name, email, country)
VALUES (1, 'Ana Silva', 'ana@example.com', 'BR')
ON CONFLICT (id) DO NOTHING;
SQL
INSERT 0 0
ON CONFLICT DO UPDATE (upsert) intermediate Insert, or update the existing row; EXCLUDED holds the values you tried to insert.

Example

bash
psql -d shop <<'SQL'
INSERT INTO customers (id, name, email, country)
VALUES (2, 'Ben Carter', 'ben.carter@example.com', 'US')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email
RETURNING id, email;
SQL
 id |         email
----+------------------------
  2 | ben.carter@example.com
(1 row)

INSERT 0 1

Tip: Add WHERE customers.email IS DISTINCT FROM EXCLUDED.email to skip updates that change nothing.

UPDATE ... WHERE beginner Change matching rows; the tag reports how many were updated.

Example

bash
psql -d shop <<'SQL'
UPDATE orders SET status = 'paid' WHERE id = 3;
SQL
UPDATE 1

Tip: Run the same WHERE as a SELECT first, or do the UPDATE inside BEGIN so you can ROLLBACK.

UPDATE ... FROM intermediate Update using values from a joined table.

Example

bash
psql -d shop <<'SQL'
UPDATE orders o
SET status = 'review'
FROM customers c
WHERE c.id = o.customer_id
  AND c.country = 'CN';
SQL
UPDATE 1
UPDATE ... RETURNING intermediate See the new values of the rows you just changed.

Example

bash
psql -d shop <<'SQL'
UPDATE orders SET total = total * 1.10
WHERE status = 'pending'
RETURNING id, total;
SQL
DELETE ... RETURNING beginner Delete rows and get back what was removed.

Example

bash
psql -d shop <<'SQL'
DELETE FROM orders WHERE ordered_on < '2026-10-10' RETURNING id;
SQL
 id
----
  1
(1 row)

DELETE 1
DELETE ... USING intermediate Delete rows based on a join with another table.

Example

bash
psql -d shop <<'SQL'
DELETE FROM orders o
USING customers c
WHERE c.id = o.customer_id
  AND c.email IS NULL;
SQL
Delete in batches advanced Remove millions of old rows in small chunks to keep locks, WAL bursts and replica lag under control.

Example

bash
psql -d shop <<'SQL'
DELETE FROM events
WHERE id IN (
  SELECT id FROM events
  WHERE created_at < now() - interval '90 days'
  LIMIT 10000
);
SQL

Tip: Repeat until it reports DELETE 0; for time-series data, dropping old partitions is cheaper still.

MERGE intermediate Insert, update or delete in one statement depending on whether each source row matches.

Example

bash
psql -d shop <<'SQL'
MERGE INTO customers AS t
USING (VALUES (2, 'ben@new.example.com'), (8, 'hana@example.com')) AS s(id, email)
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET email = s.email
WHEN NOT MATCHED THEN
  INSERT (id, name, email) VALUES (s.id, 'New customer', s.email);
SQL
MERGE 2
MERGE ... RETURNING merge_action() advanced Report which action MERGE took for each row (17+), and act on target rows missing from the source.

Example

bash
psql -d shop <<'SQL'
MERGE INTO stock AS t
USING incoming AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
WHEN NOT MATCHED BY SOURCE THEN DELETE
RETURNING merge_action(), t.sku;
SQL
COPY ... FROM (server file) intermediate Bulk-load a CSV file that lives on the database server.

Example

bash
psql -d shop <<'SQL'
COPY customers (id, name, email, country)
FROM '/var/lib/postgresql/import/customers.csv'
WITH (FORMAT csv, HEADER true);
SQL

Tip: Server-side COPY needs superuser or the pg_read_server_files role; use \copy for files on your own machine.

COPY ... TO STDOUT intermediate Stream a table or query result out as CSV, compressed on the fly.

Example

bash
psql -d shop -c "COPY (SELECT * FROM orders WHERE status = 'paid') TO STDOUT WITH (FORMAT csv, HEADER)" | gzip > paid_orders.csv.gz
COPY ... ON_ERROR ignore advanced Skip rows that fail type conversion instead of aborting the whole load (17+).

Example

bash
psql -d shop <<'SQL'
COPY staging_orders FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER, ON_ERROR ignore, LOG_VERBOSITY verbose);
SQL

Tip: PostgreSQL 18 adds REJECT_LIMIT n so the load still fails if more than n rows are bad.

String & number functions

23 commands

Everyday text and math helpers. Each example works on literals, so the output shown is exactly what you will get.

upper / lower / initcap beginner Change letter case.

Example

bash
psql -d shop <<'SQL'
SELECT upper('postgres') AS up, lower('SQL') AS low, initcap('hello world') AS title;
SQL
    up    | low |    title
----------+-----+-------------
 POSTGRES | sql | Hello World
(1 row)
|| / concat / concat_ws beginner Join strings; || returns NULL if any part is NULL, concat skips NULLs, concat_ws adds a separator.

Example

bash
psql -d shop <<'SQL'
SELECT 'pg' || '18' AS joined, concat('a', NULL, 'b') AS concat, concat_ws('-', '2026', '11', '09') AS ws;
SQL
 joined | concat |     ws
--------+--------+------------
 pg18   | ab     | 2026-11-09
(1 row)
length / char_length / octet_length beginner Count characters versus bytes.

Example

bash
psql -d shop <<'SQL'
SELECT char_length('café') AS chars, octet_length('café') AS bytes;
SQL
 chars | bytes
-------+-------
     4 |     5
(1 row)
substring / left / right beginner Cut out part of a string by position or from either end.

Example

bash
psql -d shop <<'SQL'
SELECT substring('PostgreSQL' FROM 1 FOR 8) AS sub, left('PostgreSQL', 4) AS l, right('PostgreSQL', 3) AS r;
SQL
   sub    |  l   |  r
----------+------+-----
 Postgres | Post | SQL
(1 row)
position / strpos beginner Find where a substring starts (1-based, 0 when absent).

Example

bash
psql -d shop <<'SQL'
SELECT position('SQL' IN 'PostgreSQL') AS pos;
SQL
 pos
-----
   8
(1 row)
split_part beginner Take the nth field of a delimited string.

Example

bash
psql -d shop <<'SQL'
SELECT split_part('ana@example.com', '@', 2) AS domain;
SQL
   domain
-------------
 example.com
(1 row)

Tip: A negative field number counts from the end: split_part('a.b.c', '.', -1) returns c.

trim / ltrim / rtrim beginner Strip spaces, or any characters you list, from either side.

Example

bash
psql -d shop <<'SQL'
SELECT trim('  hi  ') AS t, ltrim('xxhi', 'x') AS l, rtrim('hi!!', '!') AS r;
SQL
 t  | l  | r
----+----+----
 hi | hi | hi
(1 row)
replace / translate beginner Replace substrings, or map single characters one-to-one.

Example

bash
psql -d shop <<'SQL'
SELECT replace('2026-11-09', '-', '/') AS replaced, translate('a-b_c', '-_', '  ') AS translated;
SQL
  replaced  | translated
------------+------------
 2026/11/09 | a b c
(1 row)
lpad / rpad / repeat beginner Pad to a fixed width or repeat a string.

Example

bash
psql -d shop <<'SQL'
SELECT lpad('42', 6, '0') AS padded, repeat('ab', 3) AS rep;
SQL
 padded |  rep
--------+--------
 000042 | ababab
(1 row)
regexp_replace intermediate Replace text that matches a regular expression; add the 'g' flag for every match.

Example

bash
psql -d shop <<'SQL'
SELECT regexp_replace('Order #1234 shipped', '\d+', 'N') AS masked;
SQL
      masked
------------------
 Order #N shipped
(1 row)
regexp_substr / regexp_matches intermediate Extract the part of a string that matches a pattern.

Example

bash
psql -d shop <<'SQL'
SELECT regexp_substr('ref: ABC-42', '[A-Z]+-\d+') AS ref;
SQL
  ref
--------
 ABC-42
(1 row)

Tip: regexp_substr, regexp_count, regexp_instr and regexp_like arrived in 15; regexp_matches returns capture groups as an array.

format intermediate Build strings printf-style; %I quotes identifiers and %L quotes literals safely for dynamic SQL.

Example

bash
psql -d shop <<'SQL'
SELECT format('SELECT * FROM %I WHERE name = %L', 'my table', 'O''Brien') AS stmt;
SQL
                       stmt
--------------------------------------------------
 SELECT * FROM "my table" WHERE name = 'O''Brien'
(1 row)
string_to_array / array_to_string intermediate Convert between delimited text and arrays.

Example

bash
psql -d shop <<'SQL'
SELECT string_to_array('a,b,c', ',') AS arr, array_to_string(ARRAY['x','y'], ' | ') AS txt;
SQL
   arr   |  txt
---------+-------
 {a,b,c} | x | y
(1 row)
chr / ascii beginner Convert between characters and their code points.

Example

bash
psql -d shop <<'SQL'
SELECT chr(65) AS letter, ascii('a') AS code;
SQL
 letter | code
--------+------
 A      |   97
(1 row)
md5 / sha256 intermediate Hash text, for example to compare values or build cache keys.

Example

bash
psql -d shop <<'SQL'
SELECT md5('hello') AS md5, sha256('hello'::bytea) AS sha256;
SQL
               md5                |                               sha256
----------------------------------+--------------------------------------------------------------------
 5d41402abc4b2a76b9719d911017c592 | \x2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
(1 row)

Tip: Use pgcrypto's crypt() with gen_salt('bf') for passwords, never a plain hash.

round / ceil / floor / trunc beginner Round numbers to a precision or to whole values.

Example

bash
psql -d shop <<'SQL'
SELECT round(3.14159, 2) AS r, ceil(4.1) AS c, floor(4.9) AS f, trunc(-4.7) AS t;
SQL
  r   | c | f | t
------+---+---+----
 3.14 | 5 | 4 | -4
(1 row)
Integer division and % beginner Dividing two integers truncates; make one side numeric to keep the fraction.

Example

bash
psql -d shop <<'SQL'
SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div, 7 % 2 AS modulo;
SQL
 int_div |      num_div       | modulo
---------+--------------------+--------
       3 | 3.5000000000000000 |      1
(1 row)
Math operators intermediate Square root, factorial, exponent, bitwise NOT and remainder in one go.

Example

bash
psql -d shop <<'SQL'
SELECT |/ 25 AS root, factorial(5) AS fact, 2 ^ 10 AS power, ~ 60 AS bit_not, 17 % 5 AS modulo;
SQL
 root | fact | power | bit_not | modulo
------+------+-------+---------+--------
    5 |  120 |  1024 |     -61 |      2
(1 row)

Tip: ||/ is the cube-root operator, and sqrt(), cbrt(), power() and mod() are the function forms.

greatest / least beginner Pick the largest or smallest of several values in the same row.

Example

bash
psql -d shop <<'SQL'
SELECT greatest(3, 7, 5) AS g, least(3, 7, 5) AS l;
SQL
 g | l
---+---
 7 | 3
(1 row)
Casting with :: and CAST beginner Convert between types.

Example

bash
psql -d shop <<'SQL'
SELECT '42'::int + 1 AS n, CAST('3.5' AS numeric) AS x, 1::boolean AS b;
SQL
 n  |  x  | b
----+-----+---
 43 | 3.5 | t
(1 row)
to_char (numbers) intermediate Format numbers with thousands separators and fixed decimals.

Example

bash
psql -d shop <<'SQL'
SELECT to_char(1234567.891, 'FM9,999,999.00') AS formatted;
SQL
  formatted
--------------
 1,234,567.89
(1 row)
random / random(min, max) intermediate Generate random numbers; the two-argument integer form arrived in 17.

Example

bash
psql -d shop <<'SQL'
SELECT random() AS zero_to_one, random(1, 6) AS dice;
SQL

Tip: For repeatable test data, call setseed(0.42) first in the same session.

generate_series beginner Produce a series of numbers (or timestamps) as rows.

Example

bash
psql -d shop <<'SQL'
SELECT * FROM generate_series(1, 10, 3);
SQL
 generate_series
-----------------
               1
               4
               7
              10
(4 rows)

Dates & times

16 commands

Current time, arithmetic with intervals, truncating and extracting parts, formatting and time zones. Fixed literals keep the outputs exact.

now() / current_date / clock_timestamp() beginner Get the transaction start time, today's date, or the actual wall-clock time.

Example

bash
psql -d shop <<'SQL'
SELECT now(), current_date, current_time, clock_timestamp();
SQL

Tip: now() stays the same for the whole transaction; clock_timestamp() changes on every call.

date + integer / interval beginner Add days to a date or any interval to a date or timestamp.

Example

bash
psql -d shop <<'SQL'
SELECT date '2026-11-09' + 30 AS plus_days,
       date '2026-11-09' + interval '1 month' AS plus_month,
       timestamp '2026-11-09 10:00' - interval '90 minutes' AS earlier;
SQL
 plus_days  |     plus_month      |       earlier
------------+---------------------+---------------------
 2026-12-09 | 2026-12-09 00:00:00 | 2026-11-09 08:30:00
(1 row)
Difference between dates beginner Subtracting dates gives whole days; subtracting timestamps gives an interval.

Example

bash
psql -d shop <<'SQL'
SELECT date '2026-12-25' - date '2026-11-09' AS days_left,
       timestamp '2026-11-09 18:30' - timestamp '2026-11-07 09:00' AS elapsed;
SQL
 days_left |     elapsed
-----------+-----------------
        46 | 2 days 09:30:00
(1 row)
age() beginner Get the difference as years, months and days.

Example

bash
psql -d shop <<'SQL'
SELECT age(date '2026-11-09', date '1990-05-20') AS age;
SQL
           age
-------------------------
 36 years 5 mons 20 days
(1 row)
date_trunc beginner Round a timestamp down to the start of its month, day, hour and so on.

Example

bash
psql -d shop <<'SQL'
SELECT date_trunc('month', timestamp '2026-11-09 14:35:00') AS month_start,
       date_trunc('hour',  timestamp '2026-11-09 14:35:00') AS hour_start;
SQL
     month_start     |     hour_start
---------------------+---------------------
 2026-11-01 00:00:00 | 2026-11-09 14:00:00
(1 row)
date_bin intermediate Bucket timestamps into fixed intervals such as 15 minutes (14+).

Example

bash
psql -d shop <<'SQL'
SELECT date_bin('15 minutes', timestamp '2026-11-09 14:37:00', timestamp '2026-01-01') AS bucket;
SQL
       bucket
---------------------
 2026-11-09 14:30:00
(1 row)
extract beginner Pull out one field of a date, timestamp or interval.

Example

bash
psql -d shop <<'SQL'
SELECT extract(year FROM date '2026-11-09') AS y,
       extract(dow FROM date '2026-11-09') AS dow,
       extract(epoch FROM interval '2 hours') AS secs;
SQL
  y   | dow |    secs
------+-----+-------------
 2026 |   1 | 7200.000000
(1 row)

Tip: dow runs from 0 (Sunday) to 6; isodow runs from 1 (Monday) to 7.

to_char (dates) beginner Format dates and times with template patterns.

Example

bash
psql -d shop <<'SQL'
SELECT to_char(timestamp '2026-11-09 14:05:00', 'YYYY-MM-DD HH24:MI') AS iso,
       to_char(date '2026-11-09', 'Dy, DD Mon YYYY') AS pretty;
SQL
       iso        |      pretty
------------------+------------------
 2026-11-09 14:05 | Mon, 09 Nov 2026
(1 row)
to_date / to_timestamp beginner Parse text in a known layout into a date or timestamp.

Example

bash
psql -d shop <<'SQL'
SELECT to_date('09/11/2026', 'DD/MM/YYYY') AS d;
SQL
     d
------------
 2026-11-09
(1 row)
make_date / make_interval intermediate Build dates and intervals from numeric parts.

Example

bash
psql -d shop <<'SQL'
SELECT make_date(2026, 11, 9) AS d, make_interval(days => 10, hours => 2) AS i;
SQL
     d      |        i
------------+------------------
 2026-11-09 | 10 days 02:00:00
(1 row)
AT TIME ZONE intermediate Convert between time zones; applied twice, it turns a UTC wall-clock time into local wall-clock time.

Example

bash
psql -d shop <<'SQL'
SELECT timestamp '2026-11-09 12:00' AT TIME ZONE 'UTC' AT TIME ZONE 'America/Sao_Paulo' AS sao_paulo;
SQL
      sao_paulo
---------------------
 2026-11-09 09:00:00
(1 row)
SET TIME ZONE beginner Change how timestamptz values are displayed for this session.

Example

bash
psql -d shop <<'SQL'
SET TIME ZONE 'UTC';
SHOW timezone;
SQL
SET
 TimeZone
----------
 UTC
(1 row)

Tip: timestamptz stores an absolute instant; the session time zone only affects input parsing and display.

Epoch to timestamp intermediate Convert Unix seconds to a timestamp, and back.

Example

bash
psql -d shop <<'SQL'
SELECT to_timestamp(0) AT TIME ZONE 'UTC' AS from_epoch,
       extract(epoch FROM timestamptz '2026-01-01 00:00:00+00')::bigint AS to_epoch;
SQL
     from_epoch      |  to_epoch
---------------------+------------
 1970-01-01 00:00:00 | 1767225600
(1 row)
OVERLAPS intermediate Test whether two time periods overlap.

Example

bash
psql -d shop <<'SQL'
SELECT (date '2026-11-01', date '2026-11-10') OVERLAPS (date '2026-11-05', date '2026-11-20') AS overlaps;
SQL
 overlaps
----------
 t
(1 row)
generate_series (dates) intermediate Produce a calendar of days, handy for filling gaps in reports with a LEFT JOIN.

Example

bash
psql -d shop <<'SQL'
SELECT d::date AS day
FROM generate_series(date '2026-11-01', date '2026-11-03', interval '1 day') AS d;
SQL
    day
------------
 2026-11-01
 2026-11-02
 2026-11-03
(3 rows)
Rows from the last N days beginner Filter on a moving time window.

Example

bash
psql -d shop <<'SQL'
SELECT * FROM orders WHERE ordered_on >= current_date - 30;
SELECT * FROM events WHERE created_at >= now() - interval '24 hours';
SQL

Tip: Compare the raw column against a computed bound so an index on the column can be used; avoid wrapping the column in a function.

JSON & JSONB

20 commands

Read, build, modify and index JSON documents, including JSON path queries and the SQL/JSON functions added in 16 and 17.

-> and ->> beginner Get a field as jsonb (->) or as text (->>); chain them to go deeper.

Example

bash
psql -d shop <<'SQL'
SELECT '{"user":{"name":"Ana","tags":["vip","beta"]}}'::jsonb -> 'user' ->> 'name' AS name;
SQL
 name
------
 Ana
(1 row)
#>> path beginner Fetch a nested value by a path array.

Example

bash
psql -d shop <<'SQL'
SELECT '{"a":{"b":{"c":42}}}'::jsonb #>> '{a,b,c}' AS c;
SQL
 c
----
 42
(1 row)
Array elements beginner Index JSON arrays from 0; negative indexes count from the end.

Example

bash
psql -d shop <<'SQL'
SELECT '["x","y","z"]'::jsonb ->> 1 AS second, '["x","y","z"]'::jsonb ->> -1 AS last;
SQL
 second | last
--------+------
 y      | z
(1 row)
jsonb['key'] subscripting intermediate Read (and in UPDATE, write) jsonb with array-style subscripts (14+).

Example

bash
psql -d shop <<'SQL'
SELECT ('{"a":{"b":1}}'::jsonb)['a']['b'] AS b;
SQL
 b
---
 1
(1 row)

Tip: Subscripts also work on the left of SET: UPDATE products SET attrs['color'] = '"red"' WHERE id = 1;

@> containment beginner Check whether a document contains a given key/value structure; GIN indexes accelerate it.

Example

bash
psql -d shop <<'SQL'
SELECT '{"plan":"pro","seats":5}'::jsonb @> '{"plan":"pro"}' AS is_pro;
SQL
 is_pro
--------
 t
(1 row)
? ?| ?& intermediate Test whether a key exists, any key of a list exists, or all of them do.

Example

bash
psql -d shop <<'SQL'
SELECT '{"plan":"pro"}'::jsonb ? 'plan' AS has_plan,
       '{"plan":"pro"}'::jsonb ?& ARRAY['plan','seats'] AS has_both;
SQL
 has_plan | has_both
----------+----------
 t        | f
(1 row)
jsonb_build_object / to_jsonb beginner Build JSON from values, or turn a whole row into a JSON object.

Example

bash
psql -d shop <<'SQL'
SELECT jsonb_build_object('id', 1, 'name', 'Ana', 'active', true) AS obj;
SQL
                   obj
------------------------------------------
 {"id": 1, "name": "Ana", "active": true}
(1 row)

Tip: jsonb stores keys in its own order (shorter keys first), not in the order you wrote them; use json if order matters.

to_jsonb(row) intermediate Serialize a table row; handy for audit logs and APIs.

Example

bash
psql -d shop <<'SQL'
SELECT to_jsonb(c) FROM customers c WHERE id = 1;
SQL
                                  to_jsonb
-----------------------------------------------------------------------------
 {"id": 1, "name": "Ana Silva", "email": "ana@example.com", "country": "BR"}
(1 row)
jsonb_set intermediate Replace or add a value at a path.

Example

bash
psql -d shop <<'SQL'
SELECT jsonb_set('{"a":1,"b":2}'::jsonb, '{b}', '20') AS updated;
SQL
      updated
-------------------
 {"a": 1, "b": 20}
(1 row)
|| merge and - remove intermediate Merge two objects (right side wins) or delete a key; #- deletes by path.

Example

bash
psql -d shop <<'SQL'
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb AS merged,
       '{"a":1,"b":2}'::jsonb - 'a' AS removed;
SQL
      merged      | removed
------------------+----------
 {"a": 1, "b": 2} | {"b": 2}
(1 row)
jsonb_array_elements_text intermediate Expand a JSON array into one row per element.

Example

bash
psql -d shop <<'SQL'
SELECT jsonb_array_elements_text('["vip","beta"]') AS tag;
SQL
 tag
------
 vip
 beta
(2 rows)
jsonb_each_text intermediate Expand an object into key/value rows.

Example

bash
psql -d shop <<'SQL'
SELECT key, value FROM jsonb_each_text('{"plan":"pro","seats":"5"}');
SQL
  key  | value
-------+-------
 plan  | pro
 seats | 5
(2 rows)
jsonb_agg / jsonb_object_agg intermediate Aggregate rows into a JSON array or object.

Example

bash
psql -d shop <<'SQL'
SELECT jsonb_agg(x ORDER BY x) AS arr FROM (VALUES (3), (1), (2)) AS t(x);
SQL
    arr
-----------
 [1, 2, 3]
(1 row)
jsonb_to_recordset intermediate Turn an array of objects into typed rows.

Example

bash
psql -d shop <<'SQL'
SELECT *
FROM jsonb_to_recordset('[{"id":1,"sku":"A-1"},{"id":2,"sku":"B-7"}]') AS t(id int, sku text);
SQL
 id | sku
----+-----
  1 | A-1
  2 | B-7
(2 rows)
jsonb_path_query advanced Filter inside documents with SQL/JSON path expressions.

Example

bash
psql -d shop <<'SQL'
SELECT jsonb_path_query('{"items":[{"p":5},{"p":12},{"p":30}]}', '$.items[*] ? (@.p > 10).p') AS price;
SQL
 price
-------
 12
 30
(2 rows)

Tip: The @? and @@ operators run path tests in WHERE clauses and can use a GIN index.

JSON_TABLE advanced Turn JSON into a relational table with typed columns, the SQL-standard way (17+).

Example

bash
psql -d shop <<'SQL'
SELECT jt.*
FROM JSON_TABLE(
  '[{"sku":"A-1","qty":2},{"sku":"B-7","qty":1}]'::jsonb, '$[*]'
  COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')
) AS jt;
SQL
 sku | qty
-----+-----
 A-1 |   2
 B-7 |   1
(2 rows)
JSON_VALUE / JSON_QUERY / JSON_EXISTS advanced SQL-standard accessors with a RETURNING type and error handling (17+).

Example

bash
psql -d shop <<'SQL'
SELECT JSON_VALUE('{"a":{"b":7}}'::jsonb, '$.a.b' RETURNING int) AS b;
SQL
 b
---
 7
(1 row)
IS JSON intermediate Validate that text is JSON, optionally an object or array (16+).

Example

bash
psql -d shop <<'SQL'
SELECT '{"a":1}' IS JSON OBJECT AS ok, 'nope' IS JSON AS bad;
SQL
 ok | bad
----+-----
 t  | f
(1 row)
GIN index on jsonb intermediate Index a jsonb column so @>, @? and @@ lookups stay fast.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
SELECT id, name FROM products WHERE attrs @> '{"color":"red"}';
SQL

Tip: jsonb_path_ops is smaller and faster for @> but does not support the key-exists operators; the default jsonb_ops does.

jsonb_pretty beginner Print a document indented, one key per line.

Example

bash
psql -d shop <<'SQL'
SELECT jsonb_pretty(attrs) FROM products WHERE id = 1;
SQL

Arrays & full-text search

13 commands

Native array columns and operators, plus built-in full-text search with stemming, ranking and GIN indexes.

Array literals and indexing beginner Arrays are 1-based; array_length and cardinality give their size.

Example

bash
psql -d shop <<'SQL'
SELECT (ARRAY['a','b','c'])[1] AS first,
       array_length(ARRAY['a','b','c'], 1) AS len,
       cardinality(ARRAY[[1,2],[3,4]]) AS n;
SQL
 first | len | n
-------+-----+---
 a     |   3 | 4
(1 row)
Array slices intermediate Take a range of elements with [from:to].

Example

bash
psql -d shop <<'SQL'
SELECT (ARRAY[10,20,30,40])[2:3] AS slice;
SQL
  slice
---------
 {20,30}
(1 row)
@> && = ANY beginner Contains, overlaps, and is-element-of tests.

Example

bash
psql -d shop <<'SQL'
SELECT ARRAY['vip','beta'] @> ARRAY['vip'] AS contains,
       ARRAY['vip','beta'] && ARRAY['beta','new'] AS overlaps,
       'vip' = ANY (ARRAY['vip','beta']) AS any_match;
SQL
 contains | overlaps | any_match
----------+----------+-----------
 t        | t        | t
(1 row)
array_append / array_remove / || intermediate Add, remove or concatenate elements.

Example

bash
psql -d shop <<'SQL'
SELECT array_append(ARRAY[1,2], 3) AS app,
       array_remove(ARRAY[1,2,1], 1) AS rem,
       ARRAY[1,2] || ARRAY[3,4] AS cat;
SQL
   app   | rem |    cat
---------+-----+-----------
 {1,2,3} | {2} | {1,2,3,4}
(1 row)
array_position intermediate Find the index of the first matching element.

Example

bash
psql -d shop <<'SQL'
SELECT array_position(ARRAY['a','b','c'], 'c') AS pos;
SQL
 pos
-----
   3
(1 row)
unnest ... WITH ORDINALITY intermediate Expand an array into rows, keeping each element's position.

Example

bash
psql -d shop <<'SQL'
SELECT * FROM unnest(ARRAY['x','y']) WITH ORDINALITY AS t(val, pos);
SQL
 val | pos
-----+-----
 x   |   1
 y   |   2
(2 rows)
Update an array column intermediate Append to an array in place and query rows by element.

Example

bash
psql -d shop <<'SQL'
UPDATE products SET tags = array_append(tags, 'sale') WHERE id = 1;
SELECT id, name FROM products WHERE 'sale' = ANY (tags);
SQL

Tip: Create a GIN index on tags and query with tags @> ARRAY['sale'] so the index can be used.

to_tsvector intermediate Normalise text into searchable lexemes: lower-case, stemmed, stop words removed, positions kept.

Example

bash
psql -d shop <<'SQL'
SELECT to_tsvector('english', 'The quick brown foxes jumped');
SQL
             to_tsvector
--------------------------------------
 'brown':3 'fox':4 'jump':5 'quick':2
(1 row)
@@ match intermediate Test a document against a query; & is AND, | is OR, ! is NOT.

Example

bash
psql -d shop <<'SQL'
SELECT to_tsvector('english', 'PostgreSQL makes indexing fast')
       @@ to_tsquery('english', 'index & fast') AS hit;
SQL
 hit
-----
 t
(1 row)
websearch_to_tsquery intermediate Parse search-box input with quotes for phrases and - for exclusion.

Example

bash
psql -d shop <<'SQL'
SELECT websearch_to_tsquery('english', '"connection pool" -mysql');
SQL
      websearch_to_tsquery
---------------------------------
 'connect' <-> 'pool' & !'mysql'
(1 row)
ts_rank + ORDER BY advanced Return the best matches first.

Example

bash
psql -d shop <<'SQL'
SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', 'vacuum tuning') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 10;
SQL
Generated tsvector + GIN advanced Keep a search column up to date automatically and index it.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search);
SQL

Tip: Use setweight(..., 'A') on the title part to rank title hits above body hits.

ts_headline advanced Return a snippet with the matching words highlighted.

Example

bash
psql -d shop <<'SQL'
SELECT ts_headline('english', body, websearch_to_tsquery('english', 'replication'),
                   'StartSel=<b>, StopSel=</b>, MaxWords=20')
FROM articles
WHERE search @@ websearch_to_tsquery('english', 'replication');
SQL

Indexes

18 commands

Pick the right index type, build and rebuild indexes without blocking writes, and find indexes that are unused, invalid or oversized.

CREATE INDEX beginner Add a B-tree index, the default type, suited to =, <, >, BETWEEN and ORDER BY.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
SQL
CREATE INDEX
CREATE INDEX CONCURRENTLY intermediate Build an index without blocking inserts, updates and deletes on a live table.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_ordered_on_idx ON orders (ordered_on);
SQL

Tip: It cannot run inside a transaction block. If it fails, it leaves an INVALID index behind: drop it and try again.

UNIQUE index beginner Enforce uniqueness, optionally on an expression such as a lower-cased email.

Example

bash
psql -d shop <<'SQL'
CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email));
SQL
CREATE INDEX
Multicolumn index intermediate Index several columns; column order matters for which queries can use it.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX orders_status_date_idx ON orders (status, ordered_on DESC);
SQL

Tip: Put equality-filtered columns first and range or sort columns last. PostgreSQL 18 can also skip-scan when the first column is not filtered.

Partial index intermediate Index only the rows you query, keeping the index small and fast.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX orders_pending_idx ON orders (ordered_on) WHERE status = 'pending';
SQL

Tip: The query's WHERE clause must imply the index predicate for the planner to use it.

Expression index intermediate Index the result of a function so filters on that expression use it.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX customers_lower_name_idx ON customers (lower(name));
SELECT * FROM customers WHERE lower(name) = 'ana silva';
SQL
Covering index (INCLUDE) advanced Carry extra columns in the index so queries can be answered by an index-only scan.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX orders_customer_cover_idx ON orders (customer_id) INCLUDE (total, status);
SQL
GIN index intermediate Index the inside of composite values: jsonb, arrays, full-text vectors and trigrams.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX products_tags_gin ON products USING gin (tags);
CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);
SQL

Tip: The trigram index needs CREATE EXTENSION pg_trgm and makes ILIKE '%term%' searches indexable.

GiST / SP-GiST index advanced Index ranges, geometric data and nearest-neighbour searches.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX bookings_during_gist ON bookings USING gist (during);
SQL
BRIN index advanced A tiny index for huge, naturally ordered tables such as append-only logs by timestamp.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX events_created_brin ON events USING brin (created_at);
SQL

Tip: BRIN stores only a min/max per block range, so it is kilobytes instead of gigabytes but only helps when values follow physical order.

Hash index advanced Index for equality-only lookups on large values.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX sessions_token_hash ON sessions USING hash (token);
SQL
List indexes of a table beginner See each index and its definition from the catalog.

Example

bash
psql -d shop <<'SQL'
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'orders'
ORDER BY indexname;
SQL
Index sizes intermediate Show how much disk each index of a table uses.

Example

bash
psql -d shop <<'SQL'
SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;
SQL
Unused indexes intermediate Find indexes that have never been scanned since statistics were last reset.

Example

bash
psql -d shop <<'SQL'
SELECT s.relname AS table, s.indexrelname AS index, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
SQL

Tip: Check replicas too: usage statistics are per server, and an index idle on the primary may serve reads on a standby.

Invalid indexes intermediate List indexes left INVALID by a failed concurrent build.

Example

bash
psql -d shop <<'SQL'
SELECT indexrelid::regclass AS index FROM pg_index WHERE NOT indisvalid;
SQL
REINDEX CONCURRENTLY intermediate Rebuild a bloated or corrupted index without blocking writes.

Example

bash
psql -d shop <<'SQL'
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
REINDEX TABLE CONCURRENTLY orders;
SQL
DROP INDEX CONCURRENTLY beginner Remove an index without taking a lock that blocks queries on the table.

Example

bash
psql -d shop <<'SQL'
DROP INDEX CONCURRENTLY IF EXISTS orders_pending_idx;
SQL
ALTER INDEX beginner Rename an index or move it to another tablespace.

Example

bash
psql -d shop <<'SQL'
ALTER INDEX orders_customer_id_idx RENAME TO orders_customer_idx;
SQL
ALTER INDEX

EXPLAIN & query tuning

18 commands

Read query plans, find the statements that cost the most, and tune statistics and memory for specific workloads.

EXPLAIN beginner Show the plan the optimizer picked, with estimated costs and row counts, without running the query.

Example

bash
psql -d shop <<'SQL'
EXPLAIN SELECT * FROM orders WHERE customer_id = 2;
SQL
EXPLAIN ANALYZE intermediate Run the query and show actual times and row counts next to the estimates.

Example

bash
psql -d shop <<'SQL'
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE customer_id = 2;
SQL

Tip: Since 18, ANALYZE also prints buffer usage automatically; add BUFFERS OFF to hide it.

EXPLAIN on writes, safely intermediate Profile an UPDATE or DELETE without keeping its changes.

Example

bash
psql -d shop <<'SQL'
BEGIN;
EXPLAIN (ANALYZE) DELETE FROM orders WHERE status = 'refunded';
ROLLBACK;
SQL
EXPLAIN (FORMAT JSON) advanced Get the plan as JSON for visualisers such as explain.dalibo.com or pgMustard.

Example

bash
psql -d shop -qAt > plan.json <<'SQL'
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT JSON)
SELECT * FROM orders WHERE ordered_on > '2026-10-15';
SQL
EXPLAIN (GENERIC_PLAN) advanced See the plan for a parameterised statement as a prepared query would use it (16+).

Example

bash
psql -d shop <<'SQL'
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE customer_id = $1;
SQL
pg_stat_statements: enable intermediate Turn on per-statement statistics; it requires a restart to preload the library.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
SQL
sudo systemctl restart postgresql
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SQL

Tip: ALTER SYSTEM replaces the whole list; include any libraries that are already preloaded.

Top queries by total time intermediate Find the statements that consume the most database time overall.

Example

bash
psql -d shop <<'SQL'
SELECT round(total_exec_time) AS total_ms, calls, round(mean_exec_time, 1) AS mean_ms,
       rows, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
SQL
pg_stat_statements_reset() intermediate Clear the collected statistics, for example before measuring a change.

Example

bash
psql -d shop <<'SQL'
SELECT pg_stat_statements_reset();
SQL
Sequential vs index scans intermediate Spot large tables that are mostly read by sequential scans, a hint that an index is missing.

Example

bash
psql -d shop <<'SQL'
SELECT relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 10;
SQL
Cache hit ratio intermediate Check how often reads are served from shared buffers instead of disk.

Example

bash
psql -d shop <<'SQL'
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname = current_database();
SQL

Tip: Most OLTP workloads sit above 99%; a much lower figure suggests too little shared_buffers or RAM for the working set.

Largest tables beginner List the biggest tables including their indexes and TOAST data.

Example

bash
psql -d shop <<'SQL'
SELECT relid::regclass AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS heap
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
SQL
Largest objects of any kind intermediate Rank tables, indexes and materialized views across all user schemas by size.

Example

bash
psql -d shop <<'SQL'
SELECT n.nspname || '.' || c.relname AS object, c.relkind,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20;
SQL

Tip: relkind: r table, i index, m materialized view, t TOAST table, p partitioned table.

SET work_mem for one query intermediate Give a heavy sort or hash more memory without raising it server-wide.

Example

bash
psql -d shop <<'SQL'
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT customer_id, sum(total) FROM orders GROUP BY customer_id ORDER BY 2 DESC;
COMMIT;
SQL

Tip: work_mem applies per sort or hash node, per connection, so a high global value can exhaust RAM.

ANALYZE beginner Refresh planner statistics for a table or the whole database.

Example

bash
psql -d shop <<'SQL'
ANALYZE VERBOSE orders;
ANALYZE;
SQL

Tip: Run it after bulk loads; autovacuum would get there eventually, but plans can be poor in the meantime.

SET STATISTICS / CREATE STATISTICS advanced Collect more detail for a skewed column, or teach the planner that two columns are correlated.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
CREATE STATISTICS orders_cust_status (dependencies, ndistinct) ON customer_id, status FROM orders;
ANALYZE orders;
SQL
statement_timeout intermediate Cancel any statement that runs longer than a limit, per session, role or database.

Example

bash
psql -d shop <<'SQL'
SET statement_timeout = '5s';
ALTER ROLE reporting SET statement_timeout = '2min';
SQL

Tip: Also consider idle_in_transaction_session_timeout and (17+) transaction_timeout for sessions that hold locks.

auto_explain advanced Log the plans of slow queries automatically.

Example

bash
psql -d shop <<'SQL'
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = on;
SELECT * FROM orders WHERE total > 100;
SQL

Tip: For all sessions, add auto_explain to session_preload_libraries or shared_preload_libraries instead of LOAD.

pgbench intermediate Generate a quick synthetic load to compare settings or hardware.

Example

bash
pgbench -i -s 10 bench
pgbench -c 10 -j 2 -T 60 -P 10 bench

Tip: -s 10 creates about 1 million rows in pgbench_accounts; -P prints progress every 10 seconds.

Sessions & monitoring

15 commands

See who is connected and what they are running, stop runaway queries, and read the built-in statistics views.

pg_stat_activity beginner List every session with its user, client, state and current query.

Example

bash
psql -d shop <<'SQL'
SELECT pid, datname, usename, application_name, client_addr, state,
       backend_start, query_start, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend';
SQL
Active queries only beginner Show only statements that are running right now.

Example

bash
psql -d shop <<'SQL'
SELECT pid, usename, state, now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid();
SQL
Long-running queries intermediate Find statements running for more than five minutes.

Example

bash
psql -d shop <<'SQL'
SELECT pid, now() - query_start AS runtime, usename, left(query, 80) AS query
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes'
ORDER BY runtime DESC;
SQL
Idle in transaction intermediate Find sessions that opened a transaction and went quiet, holding locks and blocking vacuum.

Example

bash
psql -d shop <<'SQL'
SELECT pid, usename, now() - xact_start AS open_for, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY open_for DESC;
SQL
Sessions by state beginner Count connections per state against the configured maximum.

Example

bash
psql -d shop <<'SQL'
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SHOW max_connections;
SQL
pg_cancel_backend() beginner Cancel a session's current query and leave the connection open.

Example

bash
psql -d shop <<'SQL'
SELECT pg_cancel_backend(12345);
SQL
pg_terminate_backend() intermediate Close a session completely, rolling back its open transaction.

Example

bash
psql -d shop <<'SQL'
SELECT pg_terminate_backend(12345);
SQL

Tip: Try pg_cancel_backend first; terminating disconnects the client, and its application may not reconnect cleanly.

Terminate all sessions on a database advanced Kick everyone off a database, for example before a restore or rename.

Example

bash
psql -d postgres <<'SQL'
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'shop_old' AND pid <> pg_backend_pid();
SQL
Wait events intermediate See what active sessions are waiting on: locks, I/O, client, and so on.

Example

bash
psql -d shop <<'SQL'
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2
ORDER BY 3 DESC;
SQL
Uptime beginner Show when the server started and how long it has been up.

Example

bash
psql -d shop <<'SQL'
SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;
SQL
pg_stat_database intermediate Per-database counters: commits, rollbacks, deadlocks, temp files and more.

Example

bash
psql -d shop <<'SQL'
SELECT datname, xact_commit, xact_rollback, deadlocks, temp_files,
       pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname IS NOT NULL;
SQL
pg_stat_io advanced Break down reads, writes and fsyncs by backend type and context (16+).

Example

bash
psql -d shop <<'SQL'
SELECT backend_type, object, context, reads, writes, extends, hits
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads DESC;
SQL
pg_stat_checkpointer advanced See whether checkpoints are triggered by time or forced by WAL volume (17+).

Example

bash
psql -d shop <<'SQL'
SELECT num_timed, num_requested, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;
SQL

Tip: Many requested checkpoints usually means max_wal_size is too small for the write rate.

Progress views intermediate Watch long maintenance commands as they run.

Example

bash
psql -d shop <<'SQL'
SELECT * FROM pg_stat_progress_vacuum;
SELECT * FROM pg_stat_progress_create_index;
SELECT * FROM pg_stat_progress_copy;
SQL
pg_stat_reset() advanced Reset the statistics counters for the current database.

Example

bash
psql -d shop <<'SQL'
SELECT pg_stat_reset();
SQL

Tip: Resetting also clears the data autovacuum uses to decide when to run; run ANALYZE afterwards.

VACUUM & maintenance

12 commands

Reclaim dead rows, keep statistics fresh, tune autovacuum per table and watch transaction-ID age.

VACUUM beginner Mark space from deleted and updated rows as reusable, without locking out reads or writes.

Example

bash
psql -d shop <<'SQL'
VACUUM orders;
VACUUM;
SQL
VACUUM (VERBOSE, ANALYZE) beginner Vacuum and refresh statistics in one pass, reporting what was done.

Example

bash
psql -d shop <<'SQL'
VACUUM (VERBOSE, ANALYZE) orders;
SQL
VACUUM FULL intermediate Rewrite a table compactly and give the space back to the operating system.

Example

bash
psql -d shop <<'SQL'
VACUUM FULL orders;
SQL

Tip: It holds an ACCESS EXCLUSIVE lock for the whole rewrite; on busy tables use the pg_repack extension instead.

VACUUM (PARALLEL n) advanced Process a table's indexes with several workers.

Example

bash
psql -d shop <<'SQL'
VACUUM (PARALLEL 4, VERBOSE) events;
SQL
vacuumdb intermediate Vacuum and analyze databases from the shell, in parallel.

Example

bash
vacuumdb --all --analyze-in-stages --jobs 4
vacuumdb -d shop --table orders --verbose

Tip: --analyze-in-stages produces usable statistics quickly after an upgrade or restore, then refines them.

Dead tuples and last vacuum intermediate See which tables have the most dead rows and when they were last vacuumed and analyzed.

Example

bash
psql -d shop <<'SQL'
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
SQL
Per-table autovacuum settings advanced Vacuum a hot table more often than the global defaults.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 1
);
SQL
ALTER TABLE
Transaction ID age advanced Check how close each database is to forced anti-wraparound vacuuming.

Example

bash
psql -d shop <<'SQL'
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_limit
FROM pg_database
ORDER BY xid_age DESC;
SQL

Tip: Autovacuum forces freezing at autovacuum_freeze_max_age (200 million by default); alert well before 1 billion.

VACUUM (FREEZE) advanced Freeze all rows now, for example on a static table after a bulk load.

Example

bash
psql -d shop <<'SQL'
VACUUM (FREEZE, ANALYZE) archive_2025;
SQL
CLUSTER advanced Physically reorder a table by an index to speed up range scans on that order.

Example

bash
psql -d shop <<'SQL'
CLUSTER orders USING orders_ordered_on_idx;
SQL

Tip: Like VACUUM FULL it locks the table exclusively, and the order is not kept for new rows.

REINDEX DATABASE CONCURRENTLY advanced Rebuild every index in a database, for example after an OS collation library upgrade.

Example

bash
psql -d shop <<'SQL'
REINDEX DATABASE CONCURRENTLY shop;
SQL
amcheck advanced Verify B-tree index structure to detect corruption.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('orders_pkey');
SQL

Tip: The shell tool pg_amcheck runs the same checks across a whole database: pg_amcheck -d shop --heapallindexed.

Transactions & locking

13 commands

Group changes atomically, choose isolation levels, build safe work queues with row locks, and find who is blocking whom.

BEGIN / COMMIT beginner Apply several changes as one unit: all of them or none.

Example

bash
psql -d shop <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SQL
ROLLBACK beginner Undo everything since BEGIN.

Example

bash
psql -d shop <<'SQL'
BEGIN;
DELETE FROM orders;
ROLLBACK;
SQL

Tip: DDL is transactional in Postgres too, so a failed migration inside BEGIN leaves no half-created tables.

SAVEPOINT intermediate Roll back part of a transaction while keeping earlier work.

Example

bash
psql -d shop <<'SQL'
BEGIN;
INSERT INTO audit_log (msg) VALUES ('import started');
SAVEPOINT before_rows;
INSERT INTO customers VALUES (1, 'Duplicate', NULL, NULL);
ROLLBACK TO SAVEPOINT before_rows;
COMMIT;
SQL
Isolation levels advanced Run a transaction at REPEATABLE READ or SERIALIZABLE instead of the default READ COMMITTED.

Example

bash
psql -d shop <<'SQL'
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts;
COMMIT;
SQL

Tip: SERIALIZABLE can fail with SQLSTATE 40001; the application must retry the whole transaction.

BEGIN READ ONLY intermediate Guarantee a transaction cannot write, useful for reports.

Example

bash
psql -d shop <<'SQL'
BEGIN READ ONLY;
SELECT count(*) FROM orders;
COMMIT;
SQL
SELECT ... FOR UPDATE intermediate Lock the rows you read so nobody else changes them until you commit.

Example

bash
psql -d shop <<'SQL'
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;
SQL
FOR UPDATE SKIP LOCKED advanced Let many workers pull jobs from one queue table without stepping on each other.

Example

bash
psql -d shop <<'SQL'
WITH next_job AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id;
SQL
NOWAIT / lock_timeout intermediate Fail fast instead of queueing behind a lock, which matters most for DDL on busy tables.

Example

bash
psql -d shop <<'SQL'
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
SQL

Tip: A DDL statement waiting for a lock also blocks every query queued behind it, so always set lock_timeout for migrations.

LOCK TABLE advanced Take an explicit table-level lock for the rest of the transaction.

Example

bash
psql -d shop <<'SQL'
BEGIN;
LOCK TABLE inventory IN SHARE ROW EXCLUSIVE MODE;
-- consistent read-modify-write here
COMMIT;
SQL
Advisory locks advanced Use application-defined locks, for example so only one cron job instance runs at a time.

Example

bash
psql -d shop <<'SQL'
SELECT pg_try_advisory_lock(42) AS got_lock;
-- ... do the work ...
SELECT pg_advisory_unlock(42);
SQL

Tip: pg_advisory_xact_lock releases itself at the end of the transaction, so a crash cannot leave it held.

Who is blocking whom intermediate Pair each waiting session with the sessions holding the lock it needs.

Example

bash
psql -d shop <<'SQL'
SELECT w.pid AS waiting_pid, left(w.query, 50) AS waiting_query,
       b.pid AS blocking_pid, left(b.query, 50) AS blocking_query
FROM pg_stat_activity w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS blk(pid) ON true
JOIN pg_stat_activity b ON b.pid = blk.pid;
SQL
pg_locks advanced Inspect the lock table directly: which relation, which mode, granted or not.

Example

bash
psql -d shop <<'SQL'
SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted
FROM pg_locks l
WHERE l.relation IS NOT NULL
ORDER BY l.granted, l.pid;
SQL
LISTEN / NOTIFY intermediate Send lightweight messages between sessions, for example to wake up a worker.

Example

bash
psql -d shop <<'SQL'
LISTEN order_events;
SELECT pg_notify('order_events', '{"order_id": 5}');
SQL

Tip: Notifications are delivered only when the sending transaction commits.

Views, functions & triggers

15 commands

Package queries as views and materialized views, write SQL and PL/pgSQL functions and procedures, and react to changes with triggers.

CREATE VIEW beginner Save a query under a name and select from it like a table.

Example

bash
psql -d shop <<'SQL'
CREATE OR REPLACE VIEW paid_orders AS
SELECT o.id, c.name, o.total, o.ordered_on
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';
SQL
CREATE VIEW

Tip: CREATE OR REPLACE can add columns at the end but cannot drop or rename existing ones.

CREATE MATERIALIZED VIEW intermediate Store a query's result for fast reads, and refresh it when needed.

Example

bash
psql -d shop <<'SQL'
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT ordered_on, sum(total) AS revenue FROM orders GROUP BY ordered_on;
CREATE UNIQUE INDEX ON daily_revenue (ordered_on);
SQL

Tip: Use \dm to list materialized views.

REFRESH MATERIALIZED VIEW CONCURRENTLY intermediate Rebuild the stored result while readers keep using the old one.

Example

bash
psql -d shop <<'SQL'
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;
SQL

Tip: CONCURRENTLY requires a unique index on the materialized view.

SQL function beginner Wrap a reusable expression; the SQL-standard body (14+) is checked when you create it.

Example

bash
psql -d shop <<'SQL'
CREATE OR REPLACE FUNCTION add_vat(price numeric, rate numeric DEFAULT 0.20)
RETURNS numeric
LANGUAGE sql IMMUTABLE
RETURN round(price * (1 + rate), 2);
SELECT add_vat(100) AS default_rate, add_vat(100, 0.07) AS reduced;
SQL
CREATE FUNCTION
 default_rate | reduced
--------------+---------
       120.00 |  107.00
(1 row)
Classic function with $$ body beginner The older string-body style, still common, with positional arguments.

Example

bash
psql -d shop <<'SQL'
CREATE FUNCTION add_ints(integer, integer) RETURNS integer
AS $$ SELECT $1 + $2 $$
LANGUAGE sql IMMUTABLE STRICT;
SELECT add_ints(5, 9);
SQL
CREATE FUNCTION
 add_ints
----------
       14
(1 row)

Tip: STRICT (RETURNS NULL ON NULL INPUT) returns NULL without running the body when any argument is NULL.

RETURNS TABLE intermediate Return a set of rows from a function and use it in FROM.

Example

bash
psql -d shop <<'SQL'
CREATE FUNCTION orders_of(cust int)
RETURNS TABLE (id int, total numeric)
LANGUAGE sql STABLE
AS $$ SELECT id, total FROM orders WHERE customer_id = cust ORDER BY id $$;
SELECT * FROM orders_of(1);
SQL
CREATE FUNCTION
 id | total
----+--------
  1 | 120.00
  2 |  35.50
(2 rows)
PL/pgSQL function intermediate Use variables, IF and loops in procedural code.

Example

bash
psql -d shop <<'SQL'
CREATE OR REPLACE FUNCTION customer_tier(cust int) RETURNS text
LANGUAGE plpgsql STABLE AS $$
DECLARE
  spent numeric;
BEGIN
  SELECT coalesce(sum(total), 0) INTO spent FROM orders WHERE customer_id = cust;
  IF spent >= 300 THEN
    RETURN 'gold';
  ELSIF spent >= 100 THEN
    RETURN 'silver';
  END IF;
  RETURN 'bronze';
END;
$$;
SELECT id, customer_tier(id) FROM customers ORDER BY id;
SQL
CREATE FUNCTION
 id | customer_tier
----+---------------
  1 | silver
  2 | gold
  3 | bronze
  4 | bronze
(4 rows)
Exception handling advanced Catch a specific error inside PL/pgSQL and respond instead of aborting.

Example

bash
psql -d shop <<'SQL'
DO $$
BEGIN
  INSERT INTO customers (id, name) VALUES (1, 'Again');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'customer 1 already exists, skipped';
END;
$$;
SQL
NOTICE:  customer 1 already exists, skipped
DO
DO block intermediate Run anonymous PL/pgSQL once, for example a one-off maintenance loop.

Example

bash
psql -d shop <<'SQL'
DO $$
DECLARE t record;
BEGIN
  FOR t IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
    EXECUTE format('ANALYZE %I', t.tablename);
  END LOOP;
END;
$$;
SQL
CREATE PROCEDURE / CALL intermediate Procedures can COMMIT inside, which suits batch jobs that process in chunks.

Example

bash
psql -d shop <<'SQL'
CREATE OR REPLACE PROCEDURE purge_old_events()
LANGUAGE plpgsql AS $$
BEGIN
  LOOP
    DELETE FROM events WHERE id IN (
      SELECT id FROM events WHERE created_at < now() - interval '90 days' LIMIT 5000);
    EXIT WHEN NOT FOUND;
    COMMIT;
  END LOOP;
END;
$$;
CALL purge_old_events();
SQL
Trigger: updated_at intermediate Stamp a column automatically on every update.

Example

bash
psql -d shop <<'SQL'
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;
CREATE TRIGGER products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
SQL
Trigger: audit log advanced Record every change to a table as JSON, with the operation type.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE audit_log (at timestamptz DEFAULT now(), tbl text, op text, old_row jsonb, new_row jsonb);
CREATE OR REPLACE FUNCTION audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (tbl, op, old_row, new_row)
  VALUES (TG_TABLE_NAME, TG_OP,
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
  RETURN NULL;
END;
$$;
CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit();
SQL
Disable / drop a trigger intermediate Pause a trigger during a bulk load, or remove it.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders DISABLE TRIGGER orders_audit;
ALTER TABLE orders ENABLE TRIGGER orders_audit;
DROP TRIGGER IF EXISTS orders_audit ON orders;
SQL
SECURITY DEFINER advanced Let callers run a function with its owner's rights; pin search_path so it cannot be hijacked.

Example

bash
psql -d shop <<'SQL'
CREATE FUNCTION reset_password_attempts(uid int) RETURNS void
LANGUAGE sql SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$ UPDATE users SET failed_logins = 0 WHERE id = uid $$;
REVOKE ALL ON FUNCTION reset_password_attempts(int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION reset_password_attempts(int) TO support;
SQL
pg_get_functiondef / DROP FUNCTION intermediate Print a function's full definition, or drop it by its argument types.

Example

bash
psql -d shop <<'SQL'
SELECT pg_get_functiondef('add_vat(numeric, numeric)'::regprocedure);
DROP FUNCTION IF EXISTS add_ints(integer, integer);
SQL

Roles, privileges & security

18 commands

Create login roles, grant the least privilege they need, use predefined roles and row-level security, and control access in pg_hba.conf.

CREATE ROLE ... LOGIN beginner Create a role that can log in, with a password and optional expiry.

Example

bash
psql -d postgres <<'SQL'
CREATE ROLE app LOGIN PASSWORD 'change-me-now' VALID UNTIL '2027-12-31';
SQL
CREATE ROLE

Tip: CREATE USER is the same as CREATE ROLE ... LOGIN.

createuser beginner Create a role from the shell and get prompted for its password.

Example

bash
createuser --pwprompt --no-superuser --no-createdb --no-createrole app
\password beginner Change a password interactively; psql hashes it before it leaves your machine.

Example

bash
psql -d postgres
# then at the prompt:
# \password app

Tip: ALTER ROLE app PASSWORD '...' works too, but the plain text can end up in logs and history.

ALTER ROLE intermediate Change attributes and per-role settings.

Example

bash
psql -d postgres <<'SQL'
ALTER ROLE app CONNECTION LIMIT 20;
ALTER ROLE app SET statement_timeout = '30s';
ALTER ROLE app NOLOGIN;
ALTER ROLE app RENAME TO app_legacy;
SQL
List roles beginner See roles and their main attributes from SQL.

Example

bash
psql -d shop <<'SQL'
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname !~ '^pg_'
ORDER BY rolname;
SQL

Tip: The pg_user view shows only login roles; \du shows all roles with memberships.

Read-only role intermediate Grant a role read access to everything in a schema, now and in the future.

Example

bash
psql -d shop <<'SQL'
CREATE ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
CREATE ROLE analyst LOGIN PASSWORD 'change-me' IN ROLE readonly;
SQL

Tip: ALTER DEFAULT PRIVILEGES applies only to objects created later by the role that runs it; add FOR ROLE app to cover tables created by app.

Read-write role intermediate Grant DML (but not DDL) on existing and future tables and sequences.

Example

bash
psql -d shop <<'SQL'
CREATE ROLE readwrite NOLOGIN;
GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
SQL
GRANT role TO user beginner Give a user the privileges of a group role.

Example

bash
psql -d shop <<'SQL'
GRANT readonly TO alice;
REVOKE readonly FROM bob;
SQL

Tip: Since 16, GRANT ... WITH INHERIT FALSE, SET TRUE lets a user switch to a role with SET ROLE without inheriting its rights automatically.

Predefined roles intermediate Use built-in roles instead of hand-written grants for common jobs.

Example

bash
psql -d shop <<'SQL'
GRANT pg_read_all_data TO auditor;
GRANT pg_monitor TO grafana;
GRANT pg_maintain TO ops;
SQL

Tip: pg_maintain (17+) allows VACUUM, ANALYZE, REINDEX, CLUSTER and REFRESH MATERIALIZED VIEW on all tables.

Column-level privileges advanced Allow reading only some columns of a table.

Example

bash
psql -d shop <<'SQL'
REVOKE SELECT ON customers FROM support;
GRANT SELECT (id, name, country) ON customers TO support;
SQL
REVOKE / lock down public intermediate Remove default access that PUBLIC (every role) has.

Example

bash
psql -d shop <<'SQL'
REVOKE ALL ON DATABASE shop FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
SQL

Tip: Databases created by 15 and later already deny CREATE on public to ordinary roles.

Check privileges intermediate Ask whether a role can do something, without trying it.

Example

bash
psql -d shop <<'SQL'
SELECT has_table_privilege('analyst', 'orders', 'SELECT') AS can_read,
       has_table_privilege('analyst', 'orders', 'DELETE') AS can_delete;
SQL
Row-level security advanced Let each tenant see only their own rows, enforced by the database.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::int);
SET app.tenant_id = '42';
SELECT * FROM invoices;
SQL

Tip: Owners and superusers bypass policies; add ALTER TABLE invoices FORCE ROW LEVEL SECURITY so the owner is filtered too.

REASSIGN OWNED / DROP OWNED / DROP ROLE advanced Hand a departing user's objects to someone else, then remove the role.

Example

bash
psql -d shop <<'SQL'
REASSIGN OWNED BY old_dev TO app;
DROP OWNED BY old_dev;
DROP ROLE old_dev;
SQL

Tip: REASSIGN and DROP OWNED work per database; run them in every database where the role owns objects.

SET ROLE intermediate Act as another role you belong to for the rest of the session.

Example

bash
psql -d shop <<'SQL'
SET ROLE readonly;
SELECT current_user;
RESET ROLE;
SQL
pg_hba.conf rules intermediate Decide who may connect from where and how they authenticate; the first matching line wins.

Example

bash
sudo tee -a /etc/postgresql/18/main/pg_hba.conf <<'EOF'
# TYPE   DATABASE  USER      ADDRESS        METHOD
local    all       postgres                 peer
hostssl  shop      app       10.0.0.0/16    scram-sha-256
host     all       all       0.0.0.0/0      reject
EOF
sudo -u postgres psql -c 'SELECT pg_reload_conf();'

Tip: Query pg_hba_file_rules to see how the server parsed the file, including any line with an error.

SCRAM passwords and md5 cleanup intermediate Make sure new passwords are SCRAM-hashed and find roles still on md5.

Example

bash
psql -d postgres <<'SQL'
SHOW password_encryption;
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';
SQL

Tip: md5 passwords are deprecated in 18 and CREATE/ALTER ROLE warn about them; reset each listed role's password to re-hash it.

Require TLS advanced Turn on SSL and refuse unencrypted remote connections.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = '/etc/ssl/pg/server.crt';
ALTER SYSTEM SET ssl_key_file = '/etc/ssl/pg/server.key';
SELECT pg_reload_conf();
SQL

Tip: Then use hostssl lines in pg_hba.conf and sslmode=verify-full on clients; check pg_stat_ssl to see which sessions are encrypted.

Backup & restore

21 commands

Logical dumps with pg_dump and pg_dumpall, restores with psql and pg_restore, physical base backups, incremental backups and point-in-time recovery.

pg_dump > file.sql beginner Dump one database as a plain SQL script.

Example

bash
pg_dump shop > shop.sql

Tip: pg_dump takes a consistent snapshot and does not block normal reads and writes while it runs.

pg_dump -c -C beginner Make the script drop and recreate the database itself when it is replayed.

Example

bash
pg_dump -c -C -F p -f shop.sql shop
psql -d postgres -f shop.sql

Tip: Because the script contains CREATE DATABASE, replay it while connected to another database such as postgres.

pg_dump -Fc beginner Dump in the compressed custom format, which pg_restore can filter, reorder and parallelise.

Example

bash
pg_dump -Fc -f shop.dump shop
pg_dump -Fd -j intermediate Dump into a directory using several parallel jobs; the fastest option for large databases.

Example

bash
pg_dump -Fd -j 4 -f shop_dir shop
pg_dump from a remote host beginner Dump a database on another server.

Example

bash
pg_dump -h db.example.com -p 5432 -U app -Fc -f shop.dump shop
pg_dump -s / -a intermediate Dump only the schema (DDL) or only the data.

Example

bash
pg_dump -s -f shop_schema.sql shop
pg_dump -a -Fc -f shop_data.dump shop
pg_dump -t / -n / -T intermediate Include or exclude particular tables or schemas; patterns are allowed.

Example

bash
pg_dump -Fc -t orders -t customers -f two_tables.dump shop
pg_dump -Fc -n sales -f sales_schema.dump shop
pg_dump -Fc -T 'audit_*' --exclude-table-data=events -f slim.dump shop

Tip: --exclude-table-data keeps a table's definition but skips its rows, ideal for huge log tables in dev copies.

pg_dump --compress intermediate Choose the compression method and level (gzip, lz4 or zstd) for custom or directory dumps.

Example

bash
pg_dump -Fc --compress=zstd:9 -f shop.dump shop
pg_dumpall intermediate Dump every database plus cluster-wide objects (roles, tablespaces) into one SQL script.

Example

bash
pg_dumpall > all_databases.sql
pg_dumpall --globals-only > globals.sql

Tip: pg_dump never includes roles; restore globals.sql first when moving databases to a new server.

psql -f (restore plain SQL) beginner Replay a plain-text dump, stopping at the first error.

Example

bash
createdb shop_restored
psql -d shop_restored -v ON_ERROR_STOP=1 -1 -f shop.sql
pg_restore -d beginner Restore a custom-format or directory dump into an existing database.

Example

bash
createdb shop_restored
pg_restore -d shop_restored -j 4 shop.dump
pg_restore --clean --if-exists intermediate Drop existing objects before recreating them, without errors for ones that are missing.

Example

bash
pg_restore -d shop --clean --if-exists --no-owner --no-privileges shop.dump

Tip: --no-owner and --no-privileges help when the target server does not have the original roles.

pg_restore -l / -L advanced List a dump's table of contents, edit it, and restore only the entries you keep.

Example

bash
pg_restore -l shop.dump > toc.list
# comment out unwanted lines with ';' in toc.list
pg_restore -d shop -L toc.list shop.dump
pg_restore -t intermediate Restore one table (and its data) from a full dump.

Example

bash
pg_restore -d shop -t orders --data-only shop.dump
pg_restore --create intermediate Create the database named in the dump, then restore into it.

Example

bash
pg_restore -C -d postgres shop.dump
Copy a database between servers intermediate Stream a dump straight into another server without an intermediate file.

Example

bash
pg_dump -Fc -h old-db.example.com -U app shop | pg_restore -h new-db.example.com -U app -d shop --no-owner
Dump from Docker beginner Run pg_dump inside the container and write the file on the host.

Example

bash
docker exec pg18 pg_dump -U postgres -Fc shop > shop.dump
docker exec -i pg18 pg_restore -U postgres -d shop_restored < shop.dump

Tip: Do not pass -t to docker exec for binary dumps; a TTY mangles the bytes.

pg_basebackup intermediate Take a physical copy of the whole cluster, including the WAL needed to make it consistent.

Example

bash
pg_basebackup -h primary.example.com -U replicator -D /backups/base_$(date +%F) -Ft -z -X stream -P
Incremental backups advanced Back up only blocks changed since a previous backup, then combine them for restore (17+).

Example

bash
psql -d postgres -c "ALTER SYSTEM SET summarize_wal = on;" -c 'SELECT pg_reload_conf();'
pg_basebackup -D /backups/full -X stream
pg_basebackup -D /backups/incr1 --incremental=/backups/full/backup_manifest
pg_combinebackup /backups/full /backups/incr1 -o /restore/data

Tip: Run pg_verifybackup on each backup directory to confirm it matches its manifest.

WAL archiving advanced Copy every finished WAL segment somewhere safe, the basis for point-in-time recovery.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'test ! -f /archive/%f && cp %p /archive/%f';
SQL
sudo systemctl restart postgresql

Tip: Dedicated tools such as pgBackRest or Barman handle retention, compression and remote storage for you.

Point-in-time recovery advanced Restore a base backup and replay archived WAL up to a moment just before an accident.

Example

bash
cat >> /restore/data/postgresql.auto.conf <<'EOF'
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-11-09 14:29:00-03'
recovery_target_action = 'promote'
EOF
touch /restore/data/recovery.signal
pg_ctl -D /restore/data start

Configuration & server admin

17 commands

Read and change settings, reload or restart the server, find its files and logs, and upgrade between major versions.

SHOW / current_setting() beginner Read one setting, or every setting with SHOW ALL.

Example

bash
psql -d shop <<'SQL'
SHOW work_mem;
SELECT current_setting('max_connections');
SHOW ALL;
SQL
pg_settings intermediate Query settings with their unit, source and whether a restart is pending.

Example

bash
psql -d shop <<'SQL'
SELECT name, setting, unit, source, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections', 'io_method');
SQL
SET / SET LOCAL / RESET beginner Change a setting for the session, for the current transaction only, or put it back.

Example

bash
psql -d shop <<'SQL'
SET work_mem = '64MB';
BEGIN;
SET LOCAL statement_timeout = '10s';
COMMIT;
RESET work_mem;
SQL
ALTER SYSTEM + pg_reload_conf() intermediate Change a server setting persistently (written to postgresql.auto.conf) and apply it without a restart.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();
SQL
ALTER SYSTEM
 pg_reload_conf
----------------
 t
(1 row)

Tip: Settings marked postmaster in pg_settings.context (shared_buffers, max_connections, listen_addresses, port) need a full restart.

ALTER SYSTEM RESET intermediate Remove an override from postgresql.auto.conf.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM RESET log_min_duration_statement;
SELECT pg_reload_conf();
SQL
Find config files and data directory beginner Ask the server where its files are.

Example

bash
psql -d postgres <<'SQL'
SHOW config_file;
SHOW hba_file;
SHOW data_directory;
SQL
listen_addresses and port intermediate Let the server accept remote connections (restart required).

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM SET port = 5432;
SQL
sudo systemctl restart postgresql

Tip: Opening listen_addresses is only half the job; pg_hba.conf and the firewall must allow the client too.

Starting-point memory settings intermediate Typical first values for a dedicated server with 16 GB of RAM.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET random_page_cost = 1.1;
SQL
sudo systemctl restart postgresql

Tip: random_page_cost = 1.1 suits SSD or NVMe storage; leave the default of 4 for spinning disks.

Useful logging settings intermediate Log slow statements, lock waits, autovacuum runs and a helpful line prefix.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET log_min_duration_statement = '1s';
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_autovacuum_min_duration = '10s';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
SELECT pg_reload_conf();
SQL
systemctl / service beginner Start, stop, restart or reload the server through the init system.

Example

bash
sudo systemctl status postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql
sudo service postgresql stop

Tip: On RHEL-family systems the unit is versioned: postgresql-18.

pg_ctl intermediate Control a server directly by data directory, for example on custom installs.

Example

bash
pg_ctl -D /var/lib/pgsql/18/data status
pg_ctl -D /var/lib/pgsql/18/data stop -m fast
pg_ctl -D /var/lib/pgsql/18/data start -l /tmp/pg.log
pg_ctl -D /var/lib/pgsql/18/data reload

Tip: Shutdown modes: smart waits for clients, fast rolls them back (the usual choice), immediate skips the checkpoint and needs crash recovery on start.

pg_lsclusters / pg_ctlcluster intermediate List and control clusters on Debian and Ubuntu, which can run several versions side by side.

Example

bash
pg_lsclusters
sudo pg_ctlcluster 18 main restart
Read the server log beginner Find recent errors in the log.

Example

bash
sudo journalctl -u postgresql-18 --since '1 hour ago'
sudo tail -f /var/log/postgresql/postgresql-18-main.log
initdb advanced Create a new cluster data directory with chosen encoding and locale.

Example

bash
initdb -D /data/pg18 --encoding=UTF8 --locale-provider=builtin --builtin-locale=C.UTF-8

Tip: Data checksums are on by default from 18; pass --no-data-checksums only if you must match an old cluster for pg_upgrade.

pg_controldata advanced Print control-file details: checkpoint location, state, timeline, checksum version.

Example

bash
sudo -u postgres pg_controldata /var/lib/pgsql/18/data
pg_upgrade advanced Move a cluster to a new major version in minutes instead of dumping and restoring.

Example

bash
/usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-17/bin --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/17/data --new-datadir=/var/lib/pgsql/18/data \
  --check
/usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-17/bin --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/17/data --new-datadir=/var/lib/pgsql/18/data \
  --link --jobs 4

Tip: Always run --check first. With --link the old cluster must not be started again once the new one has run.

pg_upgradecluster intermediate Debian and Ubuntu wrapper that upgrades a cluster, including config files and port.

Example

bash
sudo pg_upgradecluster -m upgrade --link 17 main

Replication & high availability

16 commands

Streaming replicas, replication slots, lag checks, failover, and logical replication with publications and subscriptions.

Replication role intermediate Create a role that standbys use to stream WAL from the primary.

Example

bash
psql -d postgres <<'SQL'
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';
SQL
CREATE ROLE
pg_hba.conf replication line intermediate Allow standbys to connect for replication; the database field must be the keyword replication.

Example

bash
echo 'hostssl replication replicator 10.0.0.0/16 scram-sha-256' | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
sudo -u postgres psql -c 'SELECT pg_reload_conf();'
Build a standby with pg_basebackup -R intermediate Clone the primary into an empty data directory and write the standby configuration in one step.

Example

bash
sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/18/main/*
sudo -u postgres pg_basebackup -h primary.example.com -U replicator \
  -D /var/lib/postgresql/18/main -X stream -R -C -S standby1 -P
sudo systemctl start postgresql

Tip: -R writes primary_conninfo and standby.signal; -C -S creates and uses a replication slot so the primary keeps the WAL the standby needs.

pg_stat_replication intermediate On the primary, list connected standbys with their state and lag.

Example

bash
psql -d postgres <<'SQL'
SELECT application_name, client_addr, state, sync_state,
       write_lag, flush_lag, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_gap
FROM pg_stat_replication;
SQL
Standby status intermediate On a standby, confirm it is in recovery and see how far behind it is in time.

Example

bash
psql -d postgres <<'SQL'
SELECT pg_is_in_recovery(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;
SELECT status, sender_host FROM pg_stat_wal_receiver;
SQL

Tip: replay_delay grows on an idle primary even with no real lag, because there is nothing new to replay.

pg_promote() intermediate Turn a standby into a read-write primary during a failover.

Example

bash
psql -d postgres <<'SQL'
SELECT pg_promote();
SQL

Tip: From the shell: pg_ctl promote -D /var/lib/postgresql/18/main. Make sure the old primary cannot accept writes first.

Replication slots intermediate List slots and how much WAL each is holding back.

Example

bash
psql -d postgres <<'SQL'
SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
SQL

Tip: An inactive slot retains WAL forever and can fill the disk; cap it with max_slot_wal_keep_size.

pg_drop_replication_slot() intermediate Remove a slot whose consumer is gone.

Example

bash
psql -d postgres <<'SQL'
SELECT pg_drop_replication_slot('standby_old');
SQL
Synchronous replication advanced Wait for standbys to confirm each commit, trading latency for zero data loss.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (standby1, standby2)';
SELECT pg_reload_conf();
SQL
pg_rewind advanced Resynchronise a former primary with the new one after failover instead of re-cloning it.

Example

bash
sudo -u postgres pg_rewind --target-pgdata=/var/lib/postgresql/18/main \
  --source-server='host=new-primary.example.com user=postgres dbname=postgres' --progress

Tip: Needs wal_log_hints = on or data checksums (the default for new clusters in 18).

wal_level = logical intermediate Enable logical decoding on the publisher (restart required).

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET wal_level = logical;
SQL
sudo systemctl restart postgresql
CREATE PUBLICATION intermediate Publish changes of chosen tables, a whole schema, or every table.

Example

bash
psql -d shop <<'SQL'
CREATE PUBLICATION shop_pub FOR TABLE customers, orders;
CREATE PUBLICATION sales_pub FOR TABLES IN SCHEMA sales;
CREATE PUBLICATION paid_pub FOR TABLE orders WHERE (status = 'paid');
SQL

Tip: Row filters (WHERE) and column lists on publications arrived in 15.

CREATE SUBSCRIPTION intermediate Subscribe on the target database; existing rows are copied first, then changes stream continuously.

Example

bash
psql -d shop_replica <<'SQL'
CREATE SUBSCRIPTION shop_sub
  CONNECTION 'host=primary.example.com dbname=shop user=replicator'
  PUBLICATION shop_pub;
SQL

Tip: Logical replication copies data, not DDL: create the tables on the subscriber before subscribing.

ALTER SUBSCRIPTION intermediate Pick up tables newly added to the publication, or pause and resume replication.

Example

bash
psql -d shop_replica <<'SQL'
ALTER SUBSCRIPTION shop_sub REFRESH PUBLICATION;
ALTER SUBSCRIPTION shop_sub DISABLE;
ALTER SUBSCRIPTION shop_sub ENABLE;
SQL
pg_stat_subscription intermediate Check that subscription workers are running and how recent their last message is.

Example

bash
psql -d shop_replica <<'SQL'
SELECT subname, pid, received_lsn, last_msg_receipt_time FROM pg_stat_subscription;
SQL
pg_createsubscriber advanced Convert a stopped physical standby into a logical subscriber without copying the data again (17+).

Example

bash
pg_createsubscriber -D /var/lib/postgresql/18/main \
  -P 'host=primary.example.com dbname=shop user=replicator' \
  -d shop --publication shop_pub --subscription shop_sub

Tip: Version 18 adds --all to convert every database and --clean to remove leftover publications.

Extensions & foreign data

12 commands

Install extensions and use the popular ones: trigram search, pgcrypto, unaccent, postgres_fdw, pg_cron, pgvector, PostGIS and TimescaleDB.

pg_available_extensions beginner See which extensions can be installed and which version is active.

Example

bash
psql -d shop <<'SQL'
SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;
SQL
CREATE / ALTER / DROP EXTENSION beginner Install an extension into the current database, upgrade it, or remove it.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
ALTER EXTENSION pg_trgm UPDATE;
DROP EXTENSION IF EXISTS pg_trgm;
SQL
pg_trgm intermediate Fuzzy matching and fast ILIKE '%...%' searches with trigram similarity.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT name, similarity(name, 'Ana Silv') AS score
FROM customers
WHERE name % 'Ana Silv'
ORDER BY score DESC;
SQL
pgcrypto passwords intermediate Store salted bcrypt hashes and check a login against them.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS pgcrypto;
INSERT INTO users (email, pw_hash) VALUES ('ana@example.com', crypt('s3cret!', gen_salt('bf', 12)));
SELECT id FROM users WHERE email = 'ana@example.com' AND pw_hash = crypt('s3cret!', pw_hash);
SQL
gen_random_uuid() beginner Generate random version-4 UUIDs; built in since 13, no extension needed.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE api_keys (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), label text);
SQL

Tip: PostgreSQL 18 adds uuidv7(), whose time ordering makes B-tree indexes far less fragmented.

unaccent intermediate Strip accents for accent-insensitive searching.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS unaccent;
SELECT unaccent('São Paulo');
SQL
CREATE EXTENSION
 unaccent
-----------
 Sao Paulo
(1 row)
citext intermediate A case-insensitive text type, handy for emails and usernames.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE customers ALTER COLUMN email TYPE citext;
SQL
postgres_fdw advanced Query tables on another Postgres server as if they were local.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER legacy FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.0.0.5', port '5432', dbname 'legacy');
CREATE USER MAPPING FOR app SERVER legacy OPTIONS (user 'reader', password 'change-me');
CREATE SCHEMA legacy_import;
IMPORT FOREIGN SCHEMA public LIMIT TO (invoices) FROM SERVER legacy INTO legacy_import;
SELECT count(*) FROM legacy_import.invoices;
SQL
pg_cron advanced Schedule SQL jobs inside the database with cron syntax (third-party extension).

Example

bash
psql -d postgres <<'SQL'
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('nightly-analyze', '0 3 * * *', 'ANALYZE');
SELECT jobid, jobname, schedule, command FROM cron.job;
SELECT cron.unschedule('nightly-analyze');
SQL

Tip: Install the package for your OS and add pg_cron to shared_preload_libraries before CREATE EXTENSION.

pgvector advanced Store embeddings and run nearest-neighbour searches for AI and semantic search (third-party extension).

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body text, embedding vector(3));
INSERT INTO docs (body, embedding) VALUES ('hello', '[0.1,0.2,0.3]'), ('world', '[0.9,0.1,0.0]');
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
SELECT id, body FROM docs ORDER BY embedding <=> '[0.1,0.2,0.25]' LIMIT 5;
SQL

Tip: <-> is Euclidean distance, <=> cosine distance and <#> negative inner product; the index operator class must match the operator.

PostGIS advanced Geographic types and functions, such as the distance between two cities (third-party extension).

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS postgis;
SELECT ST_Distance(
  ST_MakePoint(-46.63, -23.55)::geography,
  ST_MakePoint(-43.17, -22.91)::geography) / 1000 AS km;
SQL
TimescaleDB hypertables advanced Partition a time-series table automatically by time (third-party extension).

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE metrics (time timestamptz NOT NULL, device text, value double precision);
SELECT create_hypertable('metrics', by_range('time'));
SQL

Tip: Needs timescaledb in shared_preload_libraries; regular CREATE INDEX statements then apply to every chunk.

Partitioning

10 commands

Split big tables by range, list or hash, add and retire partitions without downtime, and confirm the planner prunes them.

PARTITION BY RANGE intermediate Create a parent table split by a time column, plus monthly partitions.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE measurements (
  device_id int NOT NULL,
  logged_at timestamptz NOT NULL,
  value     double precision
) PARTITION BY RANGE (logged_at);
CREATE TABLE measurements_2026_11 PARTITION OF measurements
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE measurements_2026_12 PARTITION OF measurements
  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
SQL
CREATE TABLE
CREATE TABLE
CREATE TABLE

Tip: The upper bound is exclusive, so consecutive ranges can share the same boundary value.

DEFAULT partition intermediate Catch rows that fit no other partition instead of rejecting them.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE measurements_default PARTITION OF measurements DEFAULT;
SQL

Tip: Keep the default partition small: attaching a new partition must scan it for rows that belong in the new range.

PARTITION BY LIST intermediate Partition by discrete values such as region.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE sales (id bigint, region text NOT NULL, amount numeric) PARTITION BY LIST (region);
CREATE TABLE sales_americas PARTITION OF sales FOR VALUES IN ('BR', 'US', 'CA');
CREATE TABLE sales_europe   PARTITION OF sales FOR VALUES IN ('DE', 'FR', 'IT');
SQL
PARTITION BY HASH advanced Spread rows evenly over a fixed number of partitions.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE sessions (id uuid NOT NULL, data jsonb) PARTITION BY HASH (id);
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);
SQL
Indexes and keys on partitioned tables intermediate An index on the parent is created on every partition, existing and future.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX ON measurements (device_id, logged_at);
ALTER TABLE measurements ADD PRIMARY KEY (device_id, logged_at);
SQL

Tip: Primary keys and unique constraints must include all partition key columns.

ATTACH PARTITION advanced Load data into a standalone table, then attach it as a partition.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE measurements_2027_01 (LIKE measurements INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurements_2027_01 ADD CONSTRAINT m_2027_01_range
  CHECK (logged_at >= '2027-01-01' AND logged_at < '2027-02-01');
ALTER TABLE measurements ATTACH PARTITION measurements_2027_01
  FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');
SQL

Tip: The matching CHECK constraint lets ATTACH skip the validation scan.

DETACH PARTITION CONCURRENTLY advanced Remove a partition from the parent without blocking queries (14+), then archive or drop it.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE measurements DETACH PARTITION measurements_2026_11 CONCURRENTLY;
DROP TABLE measurements_2026_11;
SQL
List partitions intermediate Show the partition tree with each partition's bounds.

Example

bash
psql -d shop <<'SQL'
SELECT c.relname AS partition, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'measurements'::regclass
ORDER BY 1;
SQL

Tip: SELECT * FROM pg_partition_tree('measurements') also includes nested sub-partitions and their level.

Partition pruning intermediate Confirm that a query touches only the partitions it needs.

Example

bash
psql -d shop <<'SQL'
EXPLAIN SELECT count(*) FROM measurements
WHERE logged_at >= '2026-12-01' AND logged_at < '2026-12-08';
SQL

Tip: The plan should list only measurements_2026_12; if it lists all partitions, the WHERE clause is not on the partition key.

Create a year of partitions advanced Generate monthly partitions in a loop instead of writing each one by hand.

Example

bash
psql -d shop <<'SQL'
DO $$
DECLARE m date;
BEGIN
  FOR m IN SELECT generate_series(date '2027-02-01', date '2027-12-01', interval '1 month')::date LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF measurements FOR VALUES FROM (%L) TO (%L)',
      'measurements_' || to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
  END LOOP;
END;
$$;
SQL

Tip: The pg_partman extension automates creating future partitions and dropping old ones.

New in PostgreSQL 18

20 commands

Highlights from the PostgreSQL 18 release notes, the current major version: new SQL features, faster I/O, better upgrades and new psql and tool options.

uuidv7() beginner Generate time-ordered UUIDs that insert into B-tree indexes far more efficiently than random ones.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE events_v7 (id uuid PRIMARY KEY DEFAULT uuidv7(), payload jsonb);
SELECT uuid_extract_version(uuidv7()) AS version;
SQL
CREATE TABLE
 version
---------
       7
(1 row)

Tip: uuid_extract_timestamp(id) recovers the creation time; uuidv4() is a new explicit alias for gen_random_uuid().

Virtual generated columns intermediate Generated columns are now computed when read by default, taking no disk space.

Example

bash
psql -d shop <<'SQL'
CREATE TABLE line_items (
  qty        int,
  unit_price numeric(10,2),
  line_total numeric GENERATED ALWAYS AS (qty * unit_price) VIRTUAL
);
INSERT INTO line_items (qty, unit_price) VALUES (2, 9.99);
SELECT * FROM line_items;
SQL
CREATE TABLE
INSERT 0 1
 qty | unit_price | line_total
-----+------------+------------
   2 |       9.99 |      19.98
(1 row)

Tip: Write STORED to keep the old behaviour; only stored generated columns can be indexed.

RETURNING old / new intermediate Return values from before and after the change in UPDATE, DELETE, INSERT and MERGE.

Example

bash
psql -d shop <<'SQL'
UPDATE line_items SET qty = 3
RETURNING old.qty AS old_qty, new.qty AS new_qty, new.line_total;
SQL
 old_qty | new_qty | line_total
---------+---------+------------
       2 |       3 |      29.97
(1 row)

UPDATE 1

Tip: This continues the line_items example above. In an upsert, old.* is NULL for rows that were freshly inserted.

WITHOUT OVERLAPS (temporal keys) advanced Make a primary or unique key apply per time range, so the same id can exist for non-overlapping periods.

Example

bash
psql -d shop <<'SQL'
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_rates (
  room_id  int,
  valid_at daterange,
  price    numeric(10,2),
  PRIMARY KEY (room_id, valid_at WITHOUT OVERLAPS)
);
SQL

Tip: Foreign keys can match such keys over time with REFERENCES ... (id, PERIOD valid_at).

CHECK / FOREIGN KEY ... NOT ENFORCED advanced Declare a constraint for documentation and tooling without the server checking it.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE orders ADD CONSTRAINT orders_total_sane CHECK (total < 100000) NOT ENFORCED;
SQL
ALTER TABLE
NOT NULL ... NOT VALID advanced Add a named NOT NULL constraint without scanning the table, then validate it later.

Example

bash
psql -d shop <<'SQL'
ALTER TABLE customers ADD CONSTRAINT customers_email_nn NOT NULL email NOT VALID;
UPDATE customers SET email = 'dana@example.com' WHERE email IS NULL;
ALTER TABLE customers VALIDATE CONSTRAINT customers_email_nn;
SQL

Tip: NOT NULL constraints are now stored in pg_constraint with names, like other constraints.

EXPLAIN ANALYZE shows buffers beginner Buffer hits and reads now appear by default, along with index lookup counts.

Example

bash
psql -d shop <<'SQL'
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 1;
EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM orders WHERE customer_id = 1;
SQL
Asynchronous I/O: io_method advanced Reads can be issued ahead asynchronously; choose worker (default), io_uring (Linux) or sync.

Example

bash
psql -d shop <<'SQL'
SHOW io_method;
SQL
 io_method
-----------
 worker
(1 row)

Tip: Change it with ALTER SYSTEM SET io_method = 'io_uring' and a restart; the pg_aios view lists in-flight I/O.

B-tree skip scan intermediate A multicolumn index can now help queries that do not filter on its first column.

Example

bash
psql -d shop <<'SQL'
CREATE INDEX orders_status_day_idx ON orders (status, ordered_on);
EXPLAIN SELECT * FROM orders WHERE ordered_on = '2026-11-05';
SQL

Tip: Skip scan works best when the leading column has few distinct values, such as a status.

casefold() and PG_UNICODE_FAST intermediate Full Unicode case folding for case-insensitive comparisons, plus a new fast builtin collation.

Example

bash
psql -d postgres <<'SQL'
SELECT casefold('Straße') AS folded;
CREATE DATABASE intl TEMPLATE template0 LOCALE_PROVIDER builtin BUILTIN_LOCALE 'PG_UNICODE_FAST';
SQL
array_sort / array_reverse beginner Sort or reverse an array without unnesting it.

Example

bash
psql -d shop <<'SQL'
SELECT array_sort(ARRAY[3,1,2]) AS sorted, array_reverse(ARRAY[1,2,3]) AS reversed;
SQL
 sorted  | reversed
---------+----------
 {1,2,3} | {3,2,1}
(1 row)
OAuth authentication advanced Authenticate clients with OAuth 2.0 bearer tokens through a validator module.

Example

bash
echo 'hostssl all all 0.0.0.0/0 oauth issuer="https://auth.example.com" scope="openid postgres"' | sudo tee -a /etc/postgresql/18/main/pg_hba.conf

Tip: The server needs a token validator library listed in oauth_validator_libraries; Postgres ships the hooks, not a validator.

pg_upgrade keeps statistics, adds --swap intermediate Upgraded clusters keep optimizer statistics, so they perform well straight away; --swap moves directories for the fastest switch.

Example

bash
/usr/pgsql-18/bin/pg_upgrade \
  --old-bindir=/usr/pgsql-17/bin --new-bindir=/usr/pgsql-18/bin \
  --old-datadir=/var/lib/pgsql/17/data --new-datadir=/var/lib/pgsql/18/data \
  --swap --jobs 8

Tip: Extended statistics (CREATE STATISTICS) are not carried over; run vacuumdb --missing-stats-only afterwards.

vacuumdb --missing-stats-only intermediate Analyze only the tables and columns that have no statistics yet.

Example

bash
vacuumdb --all --analyze-in-stages --missing-stats-only --jobs 4
New pg_dump / pg_restore options intermediate Dump or skip statistics, row-level security policies, schema or data.

Example

bash
pg_dump -Fc --no-policies -f shop_no_rls.dump shop
pg_dump --statistics-only -f shop_stats.sql shop
pg_restore -d shop_copy --no-statistics shop.dump
psql pipelining advanced Send several queries without waiting for each result, cutting round trips on high-latency links.

Example

bash
psql -d shop <<'SQL'
\startpipeline
SELECT $1 \bind 1 \sendpipeline
SELECT $1 \bind 2 \sendpipeline
\endpipeline
SQL
Data checksums by default intermediate initdb now enables page checksums unless told otherwise.

Example

bash
initdb -D /data/pg18
initdb -D /data/pg18_nochk --no-data-checksums

Tip: pg_upgrade requires matching checksum settings, so initialise the new cluster to match the old one.

COPY ... REJECT_LIMIT advanced Skip bad rows during COPY but still fail if there are too many of them.

Example

bash
psql -d shop <<'SQL'
COPY staging_orders FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER, ON_ERROR ignore, REJECT_LIMIT 100);
SQL
VACUUM / ANALYZE ONLY intermediate Process a partitioned parent without recursing into all of its partitions.

Example

bash
psql -d shop <<'SQL'
ANALYZE ONLY measurements;
VACUUM ONLY measurements;
SQL

Tip: Without ONLY, VACUUM and ANALYZE now also process inheritance children.

idle_replication_slot_timeout advanced Invalidate replication slots that stay idle too long so they cannot fill the disk with WAL.

Example

bash
psql -d postgres <<'SQL'
ALTER SYSTEM SET idle_replication_slot_timeout = '2d';
SELECT pg_reload_conf();
SQL

↑ ↓ navigate Enter open Esc close