764 commands · 48 cheat sheets · 6 subcategories
SQL master quick reference — Page 2
Browse 174 commands from 12 focused cheat sheets on page 2 of 4. Each example opens its matching detailed section.
Modeling and Data Types · 17 commands
PostgreSQL Custom Types, Domains, Enums, and Composites Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a constrained domain | CREATE DOMAIN app.email_address AS text CONSTRAINT email_nonempty CHECK (btrim(VALUE) <> ''); | View examples |
| Use a domain in a table | email app.email_address NOT NULL | View examples |
| Stage a domain check | ALTER DOMAIN app.email_address ADD CONSTRAINT email_length CHECK (octet_length(VALUE) <= 320) NOT VALID; | View examples |
| Validate a domain check | ALTER DOMAIN app.email_address VALIDATE CONSTRAINT email_length; | View examples |
| Create an enum type | CREATE TYPE app.order_state AS ENUM ('pending', 'paid', 'shipped', 'cancelled'); | View examples |
| Append an enum label | ALTER TYPE app.order_state ADD VALUE IF NOT EXISTS 'returned'; | View examples |
| Position an enum label | ALTER TYPE app.order_state ADD VALUE 'processing' AFTER 'pending'; | View examples |
| Rename an enum label | ALTER TYPE app.order_state RENAME VALUE 'shipped' TO 'dispatched'; | View examples |
| Order by enum semantics | SELECT id, state FROM app.orders ORDER BY state, id; | View examples |
| Create a composite type | CREATE TYPE app.money_amount AS (amount numeric(18,2), currency_code text); | View examples |
| Construct a typed composite | ROW(49.90, 'USD')::app.money_amount | View examples |
| Read a composite field | SELECT (price).amount, (price).currency_code
FROM app.products; | View examples |
| Add a composite attribute | ALTER TYPE app.money_amount ADD ATTRIBUTE tax_included boolean CASCADE; | View examples |
| Grant type usage | GRANT USAGE
ON TYPE app.order_state,
app.email_address,
app.money_amount TO app_writer; | View examples |
| Cast text explicitly | SELECT $1::text::app.order_state; | View examples |
| Inventory custom types | SELECT n.nspname, t.typname, t.typtype
FROM pg_type t
JOIN pg_namespace n
ON n.oid = t.typnamespace
WHERE n.nspname = 'app'; | View examples |
| Inspect enum labels | SELECT enumlabel, enumsortorder
FROM pg_enum
WHERE enumtypid = 'app.order_state'::regtype
ORDER BY enumsortorder; | View examples |
Modeling and Data Types · 12 commands
PostgreSQL Dates, Times, and Intervals Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Typed date | DATE '2026-08-12' | View examples |
| Local timestamp | TIMESTAMP '2026-08-12 09:30:00' | View examples |
| Instant with offset | TIMESTAMPTZ '2026-08-12 09:30:00-03:00' | View examples |
| Transaction time | CURRENT_TIMESTAMP | View examples |
| Wall-clock time | clock_timestamp() | View examples |
| Typed duration | INTERVAL '2 days 3 hours' | View examples |
| Add a duration | started_at + INTERVAL '30 minutes' | View examples |
| Render in a zone | occurred_at AT TIME ZONE 'America/Sao_Paulo' | View examples |
| Truncate to a unit | date_trunc('month', occurred_at, 'UTC') | View examples |
| Extract a field | EXTRACT(ISODOW FROM occurred_at) | View examples |
| Half-open time window | WHERE occurred_at >= $1 AND occurred_at < $2 | View examples |
| Format for display | to_char(occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') | View examples |
Modeling and Data Types · 14 commands
PostgreSQL Identity, Generated Columns, and Sequences Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Protect an identity column | id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY | View examples |
| Allow identity overrides | id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY | View examples |
| Configure the identity sequence | GENERATED ALWAYS AS IDENTITY (START
WITH 1000 INCREMENT BY 1 CACHE 20
) | View examples |
| Import explicit identity values | INSERT INTO app.accounts OVERRIDING SYSTEM VALUE (id, name)
VALUES (42, 'Ada'); | View examples |
| Regenerate imported identities | INSERT INTO app.accounts OVERRIDING USER VALUE
SELECT *
FROM staging.accounts; | View examples |
| Store a generated value | total numeric GENERATED ALWAYS AS (quantity * unit_price) STORED | View examples |
| Compute a virtual value | full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL | View examples |
| Allocate a sequence value | SELECT nextval('app.ticket_number_seq'::regclass); | View examples |
| Read this session's value | SELECT currval('app.ticket_number_seq'::regclass); | View examples |
| Create an owned sequence | CREATE SEQUENCE app.ticket_number_seq AS bigint CACHE 20 OWNED BY app.tickets.number; | View examples |
| Grant allocation access | GRANT USAGE
ON SEQUENCE app.ticket_number_seq TO app_writer; | View examples |
| Find an attached sequence | SELECT pg_get_serial_sequence('app.accounts', 'id'); | View examples |
| Plan a sequence repair | SELECT setval(pg_get_serial_sequence('app.accounts','id'), max(id), true)
FROM app.accounts; | View examples |
| Inspect generated attributes | SELECT column_name,
is_identity,
identity_generation,
is_generated
FROM information_schema.columns
WHERE table_schema = 'app'; | View examples |
Modeling and Data Types · 12 commands
PostgreSQL JSON and JSONB Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create JSONB | '{"status":"open"}'::jsonb | View examples |
| Read JSON field | payload -> 'customer' | View examples |
| Read text field | payload ->> 'status' | View examples |
| Read a nested path | payload #>> '{customer,email}' | View examples |
| Test containment | payload @> '{"status":"open"}'::jsonb | View examples |
| Test a key | payload ? 'status' | View examples |
| Match a JSON path | payload @? '$.items[*] ? (@.price > 100)' | View examples |
| Build an object | jsonb_build_object('id', id, 'name', name) | View examples |
| Aggregate JSON objects | jsonb_agg(jsonb_build_object('id', id) ORDER BY id) | View examples |
| Set a nested value | jsonb_set(payload, '{status}', '"closed"'::jsonb) | View examples |
| Expand an array | CROSS JOIN LATERAL jsonb_array_elements(payload -> 'items') AS item | View examples |
| Index JSONB operations | CREATE INDEX events_payload_gin_idx
ON events
USING GIN (payload); | View examples |
Modeling and Data Types · 16 commands
PostgreSQL Table Constraints Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a table | CREATE TABLE accounts (id bigint PRIMARY KEY, email text NOT NULL); | View examples |
| Generate identifiers | id bigint GENERATED ALWAYS AS IDENTITY | View examples |
| Require a value | name text NOT NULL | View examples |
| Supply a default | created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP | View examples |
| Validate a condition | CONSTRAINT products_price_nonnegative CHECK (price >= 0) | View examples |
| Declare a primary key | CONSTRAINT accounts_pkey PRIMARY KEY (id) | View examples |
| Use a composite key | PRIMARY KEY (order_id, product_id) | View examples |
| Require uniqueness | CONSTRAINT accounts_email_key UNIQUE (email) | View examples |
| Reference another table | FOREIGN KEY (customer_id) REFERENCES customers (id) | View examples |
| Delete dependent rows | FOREIGN KEY (order_id) REFERENCES orders (id)
ON DELETE CASCADE | View examples |
| Detach on deletion | FOREIGN KEY (manager_id) REFERENCES employees (id)
ON DELETE
SET NULL | View examples |
| Name a rule | CONSTRAINT quantity_positive CHECK (quantity > 0) | View examples |
| Add a constraint | ALTER TABLE products ADD CONSTRAINT products_sku_key UNIQUE (sku); | View examples |
| Defer existing-row validation | ALTER TABLE orders ADD CONSTRAINT orders_total_check CHECK (total >= 0) NOT VALID; | View examples |
| Validate existing rows | ALTER TABLE orders VALIDATE CONSTRAINT orders_total_check; | View examples |
| Remove a constraint | ALTER TABLE products DROP CONSTRAINT products_sku_key; | View examples |
Modeling and Data Types · 18 commands
PostgreSQL Temporary and Unlogged Tables Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a session temporary table | CREATE TEMP TABLE work_items (item_id bigint PRIMARY KEY, payload jsonb NOT NULL); | View examples |
| Preserve rows across commits | CREATE TEMP TABLE work_items (item_id bigint)
ON COMMIT PRESERVE ROWS; | View examples |
| Clear rows after each commit | CREATE TEMP TABLE work_items (item_id bigint)
ON COMMIT DELETE ROWS; | View examples |
| Drop the table at commit | CREATE TEMP TABLE work_items (item_id bigint)
ON COMMIT DROP; | View examples |
| Materialize a transaction-scoped result | CREATE TEMP TABLE recent_orders
ON COMMIT DROP AS
SELECT order_id, total
FROM app.orders
WHERE created_at >= CURRENT_DATE; | View examples |
| Copy a table shape | CREATE TEMP TABLE staged_orders (LIKE app.orders INCLUDING DEFAULTS INCLUDING CONSTRAINTS); | View examples |
| Index a populated temporary table | CREATE INDEX work_items_customer_idx
ON work_items (customer_id); | View examples |
| Collect temporary-table statistics | ANALYZE work_items; | View examples |
| Bypass temporary name shadowing | SELECT order_id FROM app.orders; | View examples |
| Restrict temporary-table creation | REVOKE TEMPORARY ON DATABASE appdb FROM PUBLIC; | View examples |
| Delegate temporary-table creation | GRANT TEMPORARY ON DATABASE appdb TO batch_worker; | View examples |
| Budget session-local temp buffers | SET temp_buffers = '64MB'; | View examples |
| Create a shared unlogged table | CREATE UNLOGGED TABLE app.ingest_buffer (event_id bigint PRIMARY KEY, payload jsonb NOT NULL); | View examples |
| Materialize an unlogged result | CREATE UNLOGGED TABLE app.daily_rollup AS
SELECT account_id, sum(amount) AS total
FROM app.ledger
GROUP BY account_id; | View examples |
| Promote data to crash-safe storage | ALTER TABLE app.ingest_buffer SET LOGGED; | View examples |
| Convert rebuildable data to unlogged | ALTER TABLE app.daily_rollup SET UNLOGGED; | View examples |
| Audit relation persistence | SELECT n.nspname, c.relname, c.relpersistence
FROM pg_class c
JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relkind = 'r'; | View examples |
| Preserve unlogged data in a logical dump | pg_dump --format=custom --file=appdb.dump appdb | View examples |
Data Change and Programmability · 15 commands
PostgreSQL COPY Import and Export Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Import a client file | \copy app.stage_order (external_id, ordered_at, amount)
FROM './orders.csv'
WITH (FORMAT csv, HEADER match) | View examples |
| Import a server file | COPY app.stage_order (external_id, ordered_at, amount)
FROM '/srv/import/orders.csv'
WITH (FORMAT csv, HEADER match); | View examples |
| Stream from a client | COPY app.stage_order (external_id, ordered_at, amount)
FROM STDIN
WITH (FORMAT csv, HEADER match); | View examples |
| Verify CSV headings | COPY app.stage_order (external_id, ordered_at, amount)
FROM STDIN
WITH (FORMAT csv, HEADER match); | View examples |
| Choose a NULL marker | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, HEADER match, NULL 'NULL'); | View examples |
| Keep empty text | COPY app.contact
FROM STDIN
WITH (FORMAT csv, HEADER match, FORCE_NOT_NULL (display_name)); | View examples |
| Declare source encoding | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, HEADER match, ENCODING 'UTF8'); | View examples |
| Create transaction staging | CREATE TEMP TABLE stage_order (LIKE app.orders INCLUDING DEFAULTS)
ON COMMIT DROP; | View examples |
| Promote valid rows | INSERT INTO app.orders (external_id, ordered_at, amount)
SELECT external_id, ordered_at, amount
FROM stage_order; | View examples |
| Bound conversion rejects | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, HEADER match, ON_ERROR ignore, REJECT_LIMIT 20); | View examples |
| Report rejected fields | COPY app.stage_order
FROM STDIN
WITH (FORMAT csv, ON_ERROR ignore, REJECT_LIMIT 20, LOG_VERBOSITY verbose); | View examples |
| Export to a client file | \copy app.orders (external_id, ordered_at, amount) TO './orders.csv'
WITH (FORMAT csv, HEADER true) | View examples |
| Export a stable projection | \copy (
SELECT external_id, ordered_at, amount
FROM app.orders
ORDER BY external_id
) TO './orders.csv'
WITH (FORMAT csv, HEADER true) | View examples |
| Monitor COPY progress | SELECT pid,
command,
type,
bytes_processed,
tuples_processed
FROM pg_stat_progress_copy; | View examples |
| Reclaim failed-load space | VACUUM (ANALYZE) app.stage_order; | View examples |
Data Change and Programmability · 14 commands
PostgreSQL Functions, Procedures, and Triggers Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a SQL function | CREATE FUNCTION app.net_total(numeric, numeric) RETURNS numeric LANGUAGE sql IMMUTABLE RETURN $1 - $2; | View examples |
| Call with named notation | SELECT app.calculate_total(subtotal => 100, discount => 5); | View examples |
| Return rows | RETURNS TABLE (order_id bigint, total numeric) | View examples |
| Create PL/pgSQL function | CREATE FUNCTION app.require_positive(value integer) RETURNS integer LANGUAGE plpgsql AS $$ ... $$; | View examples |
| Raise an error | RAISE EXCEPTION 'quantity must be positive'
USING ERRCODE = '22023'; | View examples |
| Handle an expected error | EXCEPTION WHEN unique_violation THEN ... | View examples |
| Create a procedure | CREATE PROCEDURE app.refresh_reports() LANGUAGE plpgsql AS $$ ... $$; | View examples |
| Call a procedure | CALL app.refresh_reports(); | View examples |
| Declare stable behavior | LANGUAGE sql STABLE | View examples |
| Return null on null input | RETURNS NULL ON NULL INPUT | View examples |
| Run as function owner | SECURITY DEFINER SET search_path = pg_catalog, app | View examples |
| Create trigger function | CREATE FUNCTION app.set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ ... $$; | View examples |
| Attach row trigger | CREATE TRIGGER set_updated_at BEFORE
UPDATE
ON app.items FOR EACH ROW EXECUTE FUNCTION app.set_updated_at(); | View examples |
| Drop an exact signature | DROP FUNCTION app.net_total(numeric, numeric) RESTRICT; | View examples |
Data Change and Programmability · 12 commands
PostgreSQL Pattern Matching and Full-Text Search Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Simple pattern | WHERE title LIKE 'PostgreSQL%' | View examples |
| Case-insensitive pattern | WHERE title ILIKE '%database%' | View examples |
| Literal wildcard | WHERE code LIKE 'A\_%' ESCAPE '\' | View examples |
| POSIX regex match | WHERE value ~ '^[A-Z]{2}-[0-9]+$' | View examples |
| Case-insensitive regex | WHERE value ~* '^error:' | View examples |
| Parse a document | to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')) | View examples |
| Plain user query | plainto_tsquery('english', $1) | View examples |
| Web-style query | websearch_to_tsquery('english', $1) | View examples |
| Match document and query | search_vector @@ websearch_to_tsquery('english', $1) | View examples |
| Rank a match | ts_rank_cd(search_vector, query) AS rank | View examples |
| Highlight fragments | ts_headline('english', body, query) | View examples |
| Index a search vector | CREATE INDEX articles_search_idx
ON articles
USING GIN (search_vector); | View examples |
Data Change and Programmability · 12 commands
PostgreSQL Views and Materialized Views Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a view | CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE active; | View examples |
| Replace a compatible view | CREATE OR REPLACE VIEW active_users AS
SELECT id, name
FROM users
WHERE active; | View examples |
| Enforce view predicate | CREATE VIEW open_items AS
SELECT *
FROM items
WHERE status = 'open'
WITH LOCAL CHECK OPTION; | View examples |
| Create a security barrier | CREATE VIEW safe_users
WITH (security_barrier = true) AS
SELECT id, name
FROM users; | View examples |
| Grant view access | GRANT SELECT ON active_users TO reporting_role; | View examples |
| Create stored results | CREATE MATERIALIZED VIEW sales_summary AS
SELECT day, sum(total)
FROM sales
GROUP BY day; | View examples |
| Create unpopulated | CREATE MATERIALIZED VIEW sales_summary AS
SELECT ...
WITH NO DATA; | View examples |
| Refresh completely | REFRESH MATERIALIZED VIEW sales_summary; | View examples |
| Refresh while readable | REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary; | View examples |
| Index stored results | CREATE UNIQUE INDEX sales_summary_day_uidx
ON sales_summary (day); | View examples |
| Inspect a view definition | SELECT pg_get_viewdef('active_users'::regclass, true); | View examples |
| Drop without cascading | DROP VIEW active_users RESTRICT; | View examples |
Data Change and Programmability · 17 commands
SQL Data Modification Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Insert one row | INSERT INTO customers (name, email)
VALUES ('Ada', 'ada@example.com'); | View examples |
| Insert several rows | INSERT INTO tags (name)
VALUES ('sql'), ('database'), ('backend'); | View examples |
| Insert query results | INSERT INTO customer_archive (id, name)
SELECT id, name
FROM customers
WHERE inactive = true; | View examples |
| Use a column default | INSERT INTO jobs (name, status)
VALUES ('reindex', DEFAULT); | View examples |
| Update matching rows | UPDATE customers
SET active = false
WHERE last_seen_at < DATE '2025-01-01'; | View examples |
| Update from the old value | UPDATE products
SET price = price * 1.05
WHERE category = 'books'; | View examples |
| Update from another table | UPDATE products AS p
SET price = i.price
FROM imports AS i
WHERE i.sku = p.sku; | View examples |
| Delete matching rows | DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP; | View examples |
| Delete using another table | DELETE FROM cart_items AS ci
USING products AS p
WHERE p.id = ci.product_id AND p.discontinued = true; | View examples |
| Ignore a duplicate insert | INSERT INTO tags (name)
VALUES ('sql')
ON CONFLICT (name)
DO NOTHING; | View examples |
| Upsert a row | INSERT INTO counters (key, value)
VALUES ('views', 1)
ON CONFLICT (key)
DO UPDATE SET value = counters.value + EXCLUDED.value; | View examples |
| Return changed values | INSERT INTO customers (name)
VALUES ('Ada')
RETURNING id, name; | View examples |
| Begin a transaction | BEGIN; | View examples |
| Commit a transaction | COMMIT; | View examples |
| Roll back a transaction | ROLLBACK; | View examples |
| Create a savepoint | SAVEPOINT before_optional_step; | View examples |
| Roll back to a savepoint | ROLLBACK TO SAVEPOINT before_optional_step; | View examples |
Performance and Planning · 15 commands
PostgreSQL Indexes and Query Plans Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a B-tree index | CREATE INDEX orders_customer_idx
ON orders (customer_id); | View examples |
| Enforce uniqueness | CREATE UNIQUE INDEX users_email_uidx ON users (email); | View examples |
| Index several columns | CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC); | View examples |
| Include payload columns | CREATE INDEX orders_status_idx
ON orders (status) INCLUDE (total); | View examples |
| Index a subset | CREATE INDEX orders_open_idx
ON orders (created_at)
WHERE status = 'open'; | View examples |
| Index an expression | CREATE INDEX users_lower_email_idx
ON users (lower(email)); | View examples |
| Index composite values with GIN | CREATE INDEX documents_tags_idx
ON documents
USING GIN (tags); | View examples |
| Summarize ordered storage with BRIN | CREATE INDEX events_time_brin_idx
ON events
USING BRIN (occurred_at); | View examples |
| Build without blocking writes | CREATE INDEX CONCURRENTLY orders_customer_idx
ON orders (customer_id); | View examples |
| Drop with less write blocking | DROP INDEX CONCURRENTLY orders_customer_idx; | View examples |
| Inspect estimated plan | EXPLAIN SELECT * FROM orders WHERE customer_id = 42; | View examples |
| Measure execution | EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42; | View examples |
| Return a machine-readable plan | EXPLAIN (FORMAT JSON)
SELECT *
FROM orders
WHERE customer_id = 42; | View examples |
| Refresh planner statistics | ANALYZE orders; | View examples |
| List table indexes | SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders'; | View examples |