70 commands · 5 cheat sheets · SQL
Data Change and Programmability master quick reference
Browse 70 commands from 5 focused cheat sheets on page 1 of 1. Each example opens its matching detailed section.
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 |