Cheatsheet PostgreSQL

SELECT * FROM customers;

SELECT name, email FROM customers;

SELECT COUNT(*) FROM customers;

SELECT * returns all columns. Specify columns for better performance. COUNT(*) counts rows. Avoid * in production — list only what you need.

SELECT * FROM products
LIMIT 10;

SELECT * FROM products
LIMIT 10 OFFSET 20;

-- Page 3 (20 per page):
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;

LIMIT restricts the number of rows. OFFSET skips N rows (pagination). Always use ORDER BY with pagination for consistent results. For large datasets, prefer keyset pagination.

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

UNION combines results removing duplicates. UNION ALL keeps them all (faster). Both queries must have the same number of columns and compatible types. Prefer UNION ALL if you don't need deduplication.

SELECT name AS customer, email AS contact
FROM customers AS c;

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

AS renames columns or tables temporarily. The table alias (c) simplifies JOINs. In PostgreSQL, AS is optional for tables but recommended for clarity.

SELECT name || ' ' || surname AS full_name
FROM customers;

SELECT CONCAT(name, ' ', surname)
FROM customers;

SELECT FORMAT('Hello, %s!', name)
FROM customers;

The || operator concatenates strings. CONCAT() ignores NULLs automatically. FORMAT() uses %s-style placeholders. If an operand of || is NULL, the result is NULL.

-- Standard alternative to LIMIT:
SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM products
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

FETCH FIRST N ROWS ONLY is the SQL standard (equivalent to LIMIT). OFFSET ... ROWS replaces OFFSET. Both work in PostgreSQL. Useful for portability between DBMSs. Requires ORDER BY.

SELECT DISTINCT city FROM customers;

SELECT DISTINCT ON (city)
    city, name
FROM customers
ORDER BY city, created_at DESC;

DISTINCT removes duplicate rows. DISTINCT ON is exclusive to PostgreSQL — it returns the first row of each group. Combine it with ORDER BY to control which row is chosen.

SELECT price * quantity AS total
FROM items;

SELECT price * 1.23 AS with_vat
FROM products;

SELECT ROUND(price * 1.23, 2) AS rounded
FROM products;

The operators +, -, *, / work on numeric columns. ROUND() controls decimal places. Use NUMERIC for money — never REAL or FLOAT for monetary values.

SELECT * FROM products
ORDER BY price DESC;

SELECT * FROM customers
ORDER BY city ASC, name ASC;

SELECT * FROM orders
ORDER BY created_at DESC NULLS LAST;

ORDER BY sorts results. ASC is the default, DESC reverses it. NULLS LAST places nulls at the end (by default in ASC they come first). Multiple columns are separated by commas.

SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
    WHERE total > 100
);

SELECT name, (
    SELECT COUNT(*) FROM orders o
    WHERE o.customer_id = c.id
) AS num_orders
FROM customers c;

Subqueries are queries within queries. IN (SELECT ...) filters by a set. Correlated subqueries reference the outer query (c.id). For performance, prefer JOINs or CTEs when possible.

SELECT * FROM products
WHERE price > 100;

SELECT * FROM customers
WHERE active = true;

SELECT * FROM orders
WHERE total >= 50.00;

WHERE filters rows before returning. Operators: =, >, <, >=, <=, <> (not equal). Booleans can omit = true: WHERE active.

WHERE name LIKE 'Anna%';     -- starts with
WHERE name LIKE '%silva%';  -- contains
WHERE name LIKE '_na';      -- 2nd letter = n
WHERE name ILIKE 'ana%';    -- case-insensitive

-- % = any sequence
-- _ = exactly one character

LIKE does pattern matching with wildcards. % = zero or more chars, _ = one char. ILIKE is exclusive to PostgreSQL — it ignores case. For performance, use a pg_trgm index.

WHERE code SIMILAR TO '[0-9]{4}-[A-Z]{2}';
WHERE name SIMILAR TO '%(silva|santos)%';

-- Combines LIKE + regex:
-- % and _ work like LIKE
-- | [] () work like regex

-- Simpler alternative:
WHERE code ~ '^\d{4}-[A-Z]{2}$';

SIMILAR TO is a hybrid between LIKE and regex. It supports %, _, |, []. Less used than ~ in practice. For complex patterns, prefer pure regex with ~.

SELECT * FROM products
WHERE active = true
  AND (price < 50 OR stock > 0);

SELECT * FROM customers
WHERE NOT city = 'Lisbon';

WHERE active AND price BETWEEN 10 AND 100;

AND requires all conditions. OR needs just one. Use parentheses to control precedence — AND takes priority over OR. NOT negates the condition.

SELECT * FROM customers
WHERE phone IS NULL;

SELECT * FROM customers
WHERE email IS NOT NULL;

-- COALESCE for a default value:
SELECT COALESCE(phone, 'no number')
FROM customers;

NULL is not compared with = — use IS NULL / IS NOT NULL. NULL = NULL returns NULL, not true. COALESCE() replaces NULL with a value. Essential for optional data.

WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';
WHERE created_at >= NOW() - INTERVAL '1 month';
WHERE EXTRACT(YEAR FROM created_at) = 2024;
WHERE created_at::date = CURRENT_DATE;

-- Last 30 days:
WHERE created_at > NOW() - INTERVAL '30 days';

INTERVAL does temporal arithmetic. CURRENT_DATE is today's date. NOW() includes the time. ::date casts a timestamp to a date. EXTRACT() gets parts (year, month, day). Indexes work best with direct comparisons.

SELECT * FROM products
WHERE price BETWEEN 10 AND 100;

SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Equivalent to:
WHERE price >= 10 AND price <= 100;

BETWEEN checks an inclusive range (includes the bounds). It works with numbers, dates and strings. Equivalent to >= AND <=. For dates, be careful with the upper bound — use < 2025-01-01 to include the 31st.

WHERE name ~ '^[AB]';       -- starts with A or B
WHERE name ~* '^ana';       -- case-insensitive
WHERE name !~ '[0-9]';      -- does NOT contain digits
WHERE email ~ '^[^@]+@[^@]+\.[^@]+$';

-- ~  = regex (case-sensitive)
-- ~* = regex (case-insensitive)
-- !~ = NOT regex

PostgreSQL supports native regex with ~. ~* ignores case. !~ negates. More powerful than LIKE for complex patterns. Use ^ and $ to anchor. Ideal for format validation.

SELECT * FROM customers
WHERE city IN ('Lisbon', 'Porto', 'Braga');

SELECT * FROM products
WHERE category_id NOT IN (3, 7, 9);

-- With a subquery:
WHERE id IN (SELECT customer_id FROM orders);

IN checks membership in a list. NOT IN excludes values. It accepts subqueries. Careful: NOT IN with NULLs can give unexpected results — prefer NOT EXISTS in those cases.

WHERE price > ANY (SELECT price FROM promotions);
WHERE price > ALL (SELECT price FROM competitors);

WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = customers.id
);

WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = customers.id
);

ANY compares against at least one result. ALL requires all of them. EXISTS checks existence (more efficient than IN for large subqueries). NOT EXISTS is safe with NULLs.

SELECT o.name, c.name AS customer
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- Only returns rows with a match
-- in BOTH tables

INNER JOIN returns only rows with a match in both tables. ON defines the join condition. Rows without a match are excluded. It's the most common JOIN. INNER is optional — JOIN alone is equivalent.

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.id;

SELF JOIN is a JOIN of a table with itself. Use different aliases (e and m). Classic for hierarchies (employee → manager). The FK references the table's own PK.

SELECT o.id, c.name AS customer,
       pr.name AS product, o.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products pr ON pr.id = o.product_id
JOIN categories cat ON cat.id = pr.category_id
WHERE cat.name = 'Electronics';

Chain multiple JOINs to navigate relationships. Each ON links an FK to a PK. The order of the JOINs can affect performance. The PostgreSQL optimizer usually reorders them automatically.

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Customers without orders appear
-- with o.total = NULL

LEFT JOIN keeps ALL rows from the left table, even without a match. Right-side columns become NULL when there's no correspondence. Ideal for "all customers, with or without orders".

SELECT c.name, latest.total
FROM customers c
CROSS JOIN LATERAL (
    SELECT total FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) latest;

LATERAL allows correlated subqueries in the FROM. Each row of c runs the subquery. Ideal for "top N per group". More flexible than normal subqueries. Requires CROSS JOIN or LEFT JOIN beforehand.

-- USING: when the column has the same name
SELECT * FROM orders
JOIN customers USING (customer_id);

-- NATURAL: automatic JOIN by common columns
SELECT * FROM orders
NATURAL JOIN customers;

-- Prefer explicit ON for clarity

USING (column) simplifies when both sides have the same name. NATURAL JOIN joins automatically by all common columns — risky and unclear. Always prefer an explicit ON to avoid surprises.

SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

SELECT *
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;

RIGHT JOIN keeps all rows from the right (rare — swap the order). FULL OUTER JOIN keeps all from both, with NULLs where there's no match. Useful for finding orphan records on either side.

SELECT city, COUNT(*) AS total
FROM customers
GROUP BY city;

SELECT category, AVG(price), MAX(price)
FROM products
GROUP BY category;

GROUP BY groups rows by value. Aggregate functions (COUNT, AVG, MAX) apply to each group. Columns in the SELECT must be in the GROUP BY or be aggregated. Sort afterwards with ORDER BY.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Cartesian product: all combinations
-- 3 sizes × 4 colors = 12 rows

CROSS JOIN generates the Cartesian product — each row of A with each row of B. No ON condition. Useful to generate combinations (sizes × colors). Careful with large tables: N × M rows.

SELECT city, COUNT(*) AS total
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

-- WHERE filters BEFORE grouping
-- HAVING filters AFTER grouping

HAVING filters groups (after GROUP BY). WHERE filters individual rows (before). You can't use an alias in HAVING — repeat the expression. Equivalent to a WHERE for aggregated results.

INSERT INTO customers (name, email, city)
VALUES ('Anna Silva', 'ana@mail.com', 'Lisbon');

-- Multiple rows:
INSERT INTO products (name, price)
VALUES ('Keyboard', 49.90),
       ('Mouse', 29.90),
       ('Monitor', 299.00);

INSERT INTO ... VALUES inserts rows. Specify the columns explicitly. Multiple rows are separated by commas — faster than separate INSERTs. Columns with DEFAULT or SERIAL can be omitted.

UPDATE products
SET price = 99.90
WHERE id = 5;

UPDATE products
SET price = price * 1.10,
    updated_at = NOW()
WHERE category = 'Electronics';

UPDATE ... SET modifies existing rows. Always use WHERE — without it, it updates EVERYTHING. You can reference the current value (price * 1.10). Multiple columns are separated by commas.

TRUNCATE TABLE logs;

TRUNCATE TABLE logs RESTART IDENTITY;

TRUNCATE TABLE orders, order_items CASCADE;

TRUNCATE deletes ALL rows instantly (it doesn't log row by row). RESTART IDENTITY resets SERIAL sequences. CASCADE deletes related tables. Much faster than DELETE for clearing entire tables.

INSERT INTO customers (name, email)
VALUES ('Anna', 'ana@mail.com')
RETURNING id;

INSERT INTO products (name, price)
VALUES ('Keyboard', 49.90)
RETURNING id, created_at;

RETURNING is exclusive to PostgreSQL — it returns data from the inserted row. It eliminates the need for an extra SELECT to get the generated id. It can return any column or expression. Essential for APIs.

UPDATE products
SET active = false
WHERE stock = 0
RETURNING name, id;

UPDATE customers
SET discount = 0.10
WHERE city = 'Porto'
RETURNING *;

RETURNING in an UPDATE shows AS affected rows. RETURNING * returns all columns. Useful to confirm what changed without an extra SELECT. It also works with DELETE.

-- Export to CSV:
COPY customers TO '/tmp/customers.csv'
WITH (FORMAT csv, HEADER true);

-- Import from CSV:
COPY customers (name, email)
FROM '/tmp/customers.csv'
WITH (FORMAT csv, HEADER true);

-- Via psql:
-- \copy customers TO 'file.csv' CSV HEADER

COPY is the fastest way to import/export data. FORMAT csv for CSV. HEADER true includes/skips the header. \copy in psql operates on the client (no superuser needed). Thousands of times faster than individual INSERTs.

INSERT INTO products (id, name, stock)
VALUES (1, 'Keyboard', 10)
ON CONFLICT (id) DO UPDATE
SET stock = products.stock + EXCLUDED.stock;

-- Or ignore:
ON CONFLICT (id) DO NOTHING;

ON CONFLICT implements UPSERT (insert or update). DO UPDATE SET updates if there's a conflict. EXCLUDED references the values that were going to be inserted. DO NOTHING ignores silently. Requires a UNIQUE constraint or PK.

UPDATE orders o
SET status = 'cancelled'
FROM customers c
WHERE o.customer_id = c.id
  AND c.active = false;

-- PostgreSQL-exclusive syntax
-- (does not use UPDATE ... JOIN)

PostgreSQL uses FROM for JOINs in an UPDATE (not JOIN). The updated table is not repeated in the FROM. Use an alias for clarity. Ideal for updates based on data from another table.

INSERT INTO customers_archive (name, email)
SELECT name, email FROM customers
WHERE active = false;

INSERT INTO report (city, total)
SELECT city, COUNT(*)
FROM customers
GROUP BY city;

INSERT ... SELECT copies data from a query. Don't use VALUES — the SELECT provides AS rows. The columns must be compatible in type and order. Ideal for migrations and snapshots.

DELETE FROM customers
WHERE active = false;

DELETE FROM orders
WHERE created_at < NOW() - INTERVAL '2 years';

DELETE FROM logs
RETURNING id;

DELETE FROM removes rows. Always use WHERE — without it, it deletes EVERYTHING. RETURNING shows what was deleted. It respects FKs with ON DELETE. To delete everything, prefer TRUNCATE.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(8,2) DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

SERIAL creates auto-increment (a sequence). PRIMARY KEY defines the key. NOT NULL forces a value. DEFAULT sets a default value. NUMERIC(8,2) for money with 2 decimals.

CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id INTEGER NOT NULL
);

-- Generate manually:
SELECT gen_random_uuid();

UUID generates universally unique identifiers (128-bit). gen_random_uuid() is native since PostgreSQL 13. Ideal for APIs and distributed systems. It takes 16 bytes (vs 4 for INTEGER). It's not sequential — worse for indexes.

customer_id INT REFERENCES customers(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- Options:
-- CASCADE: propagates delete/update
-- SET NULL: sets NULL
-- RESTRICT: prevents (default)
-- NO ACTION: checks at the end

REFERENCES creates the FK. ON DELETE CASCADE deletes children automatically. SET NULL nullifies the FK. RESTRICT (default) prevents a delete if children exist. Choose according to the business rule.

SMALLINT          -- -32768 to 32767
INTEGER           -- -2B to 2B (default)
BIGINT            -- very large
NUMERIC(10,2)     -- exact (money)
REAL              -- 6 digits (float)
DOUBLE PRECISION  -- 15 digits

-- NUMERIC is exact, REAL is approximate

INTEGER is the default for IDs and counts. NUMERIC is exact — use it for money. REAL/DOUBLE are approximate (rounding errors). BIGINT for very large values. Never use float for monetary values.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO posts (tags)
VALUES (ARRAY['sql', 'postgres']);

WHERE 'sql' = ANY(tags);
WHERE tags @> ARRAY['sql'];

TEXT[] creates an array column. ANY() checks whether it contains a value. @> checks whether it contains all values of the array. An alternative to N:N relation tables for simple data. GIN indexes speed up array searches.

ALTER TABLE customers
    ADD COLUMN phone VARCHAR(20);

ALTER TABLE customers
    DROP COLUMN fax;

ALTER TABLE customers
    ALTER COLUMN name SET NOT NULL;

ALTER TABLE customers
    RENAME COLUMN name TO full_name;

ALTER TABLE modifies an existing structure. ADD COLUMN adds, DROP COLUMN removes. ALTER COLUMN ... SET NOT NULL adds a constraint. RENAME renames. On large tables, ADD COLUMN with a DEFAULT can be slow.

CHAR(10)       -- fixed length (pads with spaces)
VARCHAR(255)   -- variable length (max 255)
TEXT           -- no limit (efficient!)

-- In PostgreSQL, TEXT = VARCHAR with no limit
-- No performance penalty

TEXT is the default choice in PostgreSQL — no limit and no penalty. VARCHAR(n) limits the length (validation). CHAR(n) pads with spaces (rare). Unlike other DBMSs, TEXT is the fast the VARCHAR.

DATE              -- 2024-01-15
TIME              -- 14:30:00
TIMESTAMP         -- 2024-01-15 14:30:00
TIMESTAMPTZ       -- with timezone
INTERVAL          -- duration ('7 days')

created_at TIMESTAMPTZ DEFAULT NOW()

TIMESTAMPTZ stores with timezone — always prefer this one. TIMESTAMP without timezone is ambiguous. DATE is date only, TIME is time only. INTERVAL for durations. NOW() returns the current timestamp with tz.

active BOOLEAN DEFAULT true

-- Accepted values:
-- true, 't', 'yes', '1'
-- false, 'f', 'no', '0'

WHERE active = true;
WHERE active;          -- equivalent
WHERE NOT active;      -- negation

BOOLEAN stores true/false/null. PostgreSQL accepts several representations (t/f, yes/no). WHERE active is equivalent to WHERE active = true. NULL is distinct from false — use IS NOT TRUE to include NULLs.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    price NUMERIC CHECK (price >= 0),
    category_id INT REFERENCES categories(id),
    CONSTRAINT unique_name UNIQUE (name, category_id)
);

PRIMARY KEY = UNIQUE + NOT NULL. UNIQUE prevents duplicates. CHECK validates conditions. REFERENCES creates an FK. CONSTRAINT name gives an explicit name to make DROP easier. Constraints are validated on every INSERT/UPDATE.

WITH active_customers AS (
    SELECT id, name FROM customers
    WHERE active = true
)
SELECT ac.name, COUNT(o.id) AS orders
FROM active_customers ac
JOIN orders o ON o.customer_id = ac.id
GROUP BY ac.name;

WITH creates a CTE (Common Table Expression) — a named temporary query. It improves readability of complex queries. It exists only during the query. It can be referenced multiple times. It's not materialized by default (PostgreSQL 12+).

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO events (data) VALUES
('{"type": "click", "page": "/home"}'),
('{"type": "view", "duration": 30}');

JSONB stores JSON in binary format (faster than JSON). It supports indexes and query operators. Ideal for semi-structured data. It validates the JSON on insert. Prefer JSONB over JSON in most cases.

ALTER TABLE posts ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english', title || ' ' || body)
    ) STORED;

CREATE INDEX idx_search ON posts USING GIN (search);

SELECT * FROM posts
WHERE search @@ to_tsquery('english', 'sql & tutorial');

tsvector converts text into search tokens. to_tsquery builds the search query. @@ is the match operator. A GIN index speeds it up. Supports stemming and ranking. A native alternative to Elasticsearch for simple searches.

WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

WITH RECURSIVE allows recursion in SQL. The anchor part starts, the recursive part references the CTE itself. UNION ALL combines the results. Ideal for hierarchies, trees and graphs. Always include a stopping condition.

SELECT data->>'type' AS type
FROM events;

SELECT * FROM events
WHERE data @> '{"type": "click"}';

SELECT data->'user'->>'name'
FROM events;

-- Nested path:
SELECT data #>> '{user, address, city}'
FROM events;

->> extracts a value the text. -> extracts the JSON. @> checks containment (uses a GIN index). #>> accesses nested paths. Combine with WHERE to filter JSON documents efficiently.

BEGIN;

UPDATE accounts SET balance = balance - 100
WHERE id = 1;

UPDATE accounts SET balance = balance + 100
WHERE id = 2;

COMMIT;
-- Or ROLLBACK; to undo everything

BEGIN starts a transaction. COMMIT confirms it, ROLLBACK undoes it. It guarantees atomicity — all or nothing. Essential for multi-table operations. SAVEPOINT allows a partial rollback. PostgreSQL uses MVCC by default.

SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    AVG(salary) OVER () AS overall_avg
FROM employees;

-- PARTITION BY groups the window:
SELECT dept, name, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
FROM employees;

OVER() defines the window. ORDER BY inside OVER sorts. PARTITION BY resets the calculation per group. It does not collapse rows like GROUP BY. RANK(), ROW_NUMBER(), DENSE_RANK() are the most common.

CREATE INDEX idx_email ON customers(email);

CREATE INDEX idx_data ON events USING GIN (data);

CREATE UNIQUE INDEX idx_slug ON posts(slug);

CREATE INDEX idx_created ON orders(created_at DESC);

DROP INDEX idx_email;

CREATE INDEX speeds up WHERE and ORDER BY. B-tree is the default (comparisons). GIN for JSONB, arrays and full-text. UNIQUE prevents duplicates. Indexes speed up reads but slow down writes. Don't create too many.

-- Sequence of numbers:
SELECT * FROM generate_series(1, 10);

-- Sequence with a step:
SELECT * FROM generate_series(0, 100, 10);

-- Sequence of dates (useful for reports):
SELECT d::date
FROM generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 month'::interval
) AS d;

-- Fill gaps: LEFT JOIN with AS series
-- ensures all days/months in the result

generate_series() generates sequences of numbers, dates or timestamps. Essential for reports without gaps — do a LEFT JOIN of the series with the data to include periods with no records. Accepts a step (increment) the the third argument.

SELECT name, salary,
    ROW_NUMBER() OVER (ORDER BY id) AS num,
    LAG(salary) OVER (ORDER BY id) AS previous,
    LEAD(salary) OVER (ORDER BY id) AS next
FROM employees;

-- Difference from the previous row:
SELECT salary - LAG(salary) OVER (ORDER BY id)
FROM employees;

ROW_NUMBER() numbers sequentially. LAG() accesses the previous row, LEAD() the next. Ideal for temporal comparisons and deltas. They accept an offset: LAG(col, 2) = 2 rows back.

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers
WHERE active = true;

CREATE MATERIALIZED VIEW sales_report AS
SELECT city, SUM(total) FROM orders
GROUP BY city;

REFRESH MATERIALIZED VIEW sales_report;

VIEW is a stored query (runs on every access). MATERIALIZED VIEW stores the result physically — faster but stale. REFRESH updates the data. Ideal for heavy reports queried frequently.

-- Aggregate only a subset of rows:
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,
  SUM(total) FILTER (WHERE status = 'paid') AS revenue,
  AVG(total) FILTER (WHERE total > 0) AS avg
FROM orders;

-- Equivalent to CASE inside the aggregate,
-- but more readable:
-- SUM(CASE WHEN status='paid' THEN total END)

-- Works with any aggregate:
-- COUNT, SUM, AVG, array_agg, etc.

FILTER (WHERE ...) applies an aggregate only to the rows that meet the condition — more readable than CASE inside the aggregate. It works with COUNT, SUM, AVG, array_agg, etc. Exclusive to PostgreSQL / standard SQL.

SELECT
    COUNT(*) AS total,
    SUM(price) AS sum,
    AVG(price) AS avg,
    MIN(price) AS min,
    MAX(price) AS max
FROM products;

COUNT(*) counts rows. SUM, AVG, MIN, MAX operate on numeric columns. They ignore NULLs (except COUNT(*)). Combine with GROUP BY for per-group aggregations.

SELECT '123'::INTEGER;
SELECT 123::TEXT;
SELECT '2024-01-15'::DATE;
SELECT price::NUMERIC(10,2);

-- Standard equivalent:
SELECT CAST('123' AS INTEGER);

:: is the PostgreSQL syntax for type conversion. It is equivalent to CAST(x AS type). It converts strings to numbers, dates, etc. If the conversion fails, it raises an error. Useful for comparing different types.

SELECT GREATEST(10, 20, 5);   -- 20
SELECT LEAST(10, 20, 5);      -- 5

SELECT WIDTH_BUCKET(price, 0, 100, 5)
FROM products;  -- bucket 1-5

-- IIF does not exist; use CASE or:
SELECT (price > 100)::TEXT FROM products;

GREATEST/LEAST return the largest/smallest of a list. WIDTH_BUCKET distributes into intervals. PostgreSQL has no IIF() — use CASE. Casting a boolean to text returns true/false.

SELECT STRING_AGG(name, ', ' ORDER BY name)
FROM customers;

SELECT ARRAY_AGG(DISTINCT city)
FROM customers;

-- Result: "Anna, Brian, Carla"
-- Result: {Lisbon, Porto, Braga}

STRING_AGG() concatenates values with a separator. ARRAY_AGG() collects values into an array. Both accept an internal ORDER BY. DISTINCT removes duplicates. Useful for reports and lists.

SELECT COALESCE(phone, 'no number')
FROM customers;

SELECT COALESCE(a, b, c, 'default');

SELECT NULLIF(price, 0);
-- Returns NULL if price = 0

-- Avoid division by zero:
SELECT total / NULLIF(qty, 0) FROM items;

COALESCE() returns the first non-NULL value. NULLIF(a, b) returns NULL if a = b. Combine them to avoid division by zero. COALESCE accepts multiple arguments. Essential for default values.

SELECT current_database();
SELECT current_user;
SELECT version();
SELECT pg_size_pretty(pg_database_size('store'));
SELECT pg_backend_pid();

-- Table size:
SELECT pg_size_pretty(pg_total_relation_size('orders'));

current_database() and current_user show the context. version() returns the PostgreSQL version. pg_size_pretty() formats readable sizes. pg_total_relation_size() includes indexes. Useful for administration and monitoring.

UPPER('hello')         -- 'HELLO'
LOWER('HELLO')         -- 'hello'
LENGTH('hello')        -- 5
TRIM('  hello  ')      -- 'hello'
SUBSTRING('hello' FROM 1 FOR 2)  -- 'he'
REPLACE('hello', 'l', 'L')       -- 'heLLo'
INITCAP('ana silva')   -- 'Anna Silva'

UPPER/LOWER change case. LENGTH counts characters. TRIM removes spaces. SUBSTRING extracts a part. INITCAP capitalizes each word. PostgreSQL uses 1-based positions.

SELECT name,
    CASE
        WHEN price > 100 THEN 'expensive'
        WHEN price > 50 THEN 'medium'
        ELSE 'cheap'
    END AS category
FROM products;

-- Simple form:
CASE status WHEN 'A' THEN 'Active'
            WHEN 'I' THEN 'Inactive' END

CASE WHEN implements conditional logic in the SELECT. It works like if/else. The simple form compares one value. Always end with END. You can use it in WHERE, ORDER BY and GROUP BY. It returns NULL if no case matches and there is no ELSE.

NOW()                          -- current timestamp
CURRENT_DATE                   -- current date
EXTRACT(YEAR FROM created_at)  -- 2024
TO_CHAR(created_at, 'DD/MM/YYYY')  -- '15/01/2024'
DATE_TRUNC('month', created_at)    -- 1st of the month
created_at + INTERVAL '7 days'     -- +7 days

NOW() returns a timestamp with timezone. EXTRACT() gets parts (YEAR, MONTH, DAY). TO_CHAR() formats the a string. DATE_TRUNC() rounds to a unit. Arithmetic with INTERVAL is intuitive.

ROUND(3.14159, 2)   -- 3.14
CEIL(4.1)           -- 5
FLOOR(4.9)          -- 4
ABS(-5)             -- 5
MOD(10, 3)          -- 1
POWER(2, 10)        -- 1024
RANDOM()            -- 0.0 to 1.0

ROUND() rounds to N places. CEIL/FLOOR round up/down. ABS() absolute value. MOD() division remainder. RANDOM() generates a random float. Use ORDER BY RANDOM() for samples (slow on large tables).

psql -U postgres -d my_db
psql -h localhost -p 5432 -U user -d db

# With password (asks interactively):
psql -U postgres -W -d db

# Connection string:
psql "postgresql://user:pass@host:5432/db"

psql is the PostgreSQL CLI client. -U user, -d database, -h host, -p port. -W forces a password prompt. Connection strings are handy for scripts and automation.

# SQL backup:
pg_dump -U postgres store > backup.sql

# Custom backup (compressed, selective):
pg_dump -U postgres -Fc store > backup.dump

# Restore SQL:
psql -U postgres store < backup.sql

# Restore custom:
pg_restore -U postgres -d store backup.dump

pg_dump exports a database. -Fc custom format (compressed, allows a selective restore). pg_restore imports the custom format. pg_dumpall exports all databases. Always test restores. Schedule backups with cron.

VACUUM ANALYZE customers;

VACUUM FULL customers;  -- rewrites the table (lock)

ANALYZE customers;      -- statistics only

-- Autovacuum (configuration):
-- autovacuum = on (default)
-- autovacuum_naptime = 60s

VACUUM reclaims space from dead rows (MVCC). ANALYZE updates statistics for the planner. VACUUM FULL rewrites the table (exclusive lock — avoid in production). autovacuum runs automatically by default.

\l          -- list databases
\dt         -- list tables
\d table    -- describe table
\du         -- list roles
\di         -- list indexes
\dn         -- list schemas
\x          -- expanded mode
\timing     -- show execution time
\q          -- quit

Meta-commands start with \ (they are not SQL). \dt lists tables, \d table shows the structure. \timing shows the duration of each query. \x toggles vertical format (useful for wide rows). \? shows help.

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY
);

SET search_path TO sales, public;

SELECT * FROM sales.orders;

SCHEMA organizes tables into logical namespaces. public is the default schema. search_path sets the lookup order. Useful for multi-tenancy or module separation. Permissions can be per schema.

CREATE TABLESPACE ssd LOCATION '/mnt/ssd/pgdata';

CREATE TABLE hot_data (...)
    TABLESPACE ssd;

-- WAL (Write-Ahead Log):
-- pg_wal/ contains transaction logs
-- archive_mode = on  (for PITR)
-- wal_level = replica (for streaming)

TABLESPACE lets you store data on different disks. Useful for separating hot/cold data. WAL guarantees durability — it writes before the data. archive_mode for point-in-time recovery. Essential for HA and disaster recovery.

CREATE DATABASE store
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    TEMPLATE template0;

-- Connect:
\c store

-- Drop:
DROP DATABASE store;

CREATE DATABASE creates a new database. ENCODING UTF8 is the recommended default. TEMPLATE0 for an encoding different from the template. \c switches database in psql. DROP DATABASE is irreversible — be careful.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- List available:
SELECT * FROM pg_available_extensions;

-- List installed:
SELECT * FROM pg_extension;

CREATE EXTENSION enables extra features. pg_trgm for fuzzy search. postgis for geographic data. uuid-ossp for UUIDs (legacy). Requires superuser. Extensions are installed per database.

# Logical backup (SQL):
pg_dump -U postgres -d store > store.sql

# Custom format (binary, compression):
pg_dump -U postgres -Fc store > store.dump

# Restore from the custom format:
pg_restore -U postgres -d store store.dump

# Schema only (no data):
pg_dump -U postgres --schema-only store > schema.sql

# Backup of ALL databases:
pg_dumpall -U postgres > all.sql

# pg_dump does not block reads

pg_dump makes a logical backup without blocking reads. The -Fc (custom) format is binary, compressed and allows a selective restore with pg_restore. --schema-only exports only the structure. pg_dumpall copies all databases.

CREATE ROLE anna WITH LOGIN PASSWORD 'secret';
CREATE ROLE admin WITH SUPERUSER;

GRANT ALL PRIVILEGES ON DATABASE store TO anna;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO anna;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO anna;

REVOKE DELETE ON customers FROM anna;

CREATE ROLE creates a user/group. WITH LOGIN allows connection. GRANT gives permissions, REVOKE removes them. SUPERUSER has full access. Permissions are per object (table, schema, sequence).

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_user_tables;

-- Active slow queries:
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Cancel a query:
SELECT pg_cancel_backend(pid);

pg_stat_activity shows active connections and queries. pg_stat_user_tables usage statistics. pg_cancel_backend() cancels a query. pg_terminate_backend() kills the connection. Essential for debugging in production.

# File: data/pg_hba.conf
# Controls who can connect and how

# TYPE  DATABASE  USER  ADDRESS        METHOD
local   all       all                  peer
host    all       all   127.0.0.1/32   scram-sha-256
host    store     anna  192.168.1.0/24 scram-sha-256
host    all       all   0.0.0.0/0      reject

# Reload without restarting:
SELECT pg_reload_conf();

pg_hba.conf controls access: type (local/host), database, user, address and method. scram-sha-256 is the recommended password method. peer uses the OS user. pg_reload_conf() applies changes without restarting the service.

EXPLAIN ANALYZE
SELECT * FROM products
WHERE price > 100;

-- Look for:
-- "Seq Scan" = no index (slow)
-- "Index Scan" = uses index (fast)
-- "actual time" = real time
-- "rows" = rows processed

EXPLAIN shows the execution plan. ANALYZE runs it and shows real times. Seq Scan indicates a missing index. Index Scan is the desired one. Check rows vs actual rows for wrong estimates. The #1 optimization tool.

-- Strings: single quotes
WHERE name = 'Anna';

-- Identifiers: double quotes
SELECT "Name" FROM "Customers";

-- PostgreSQL converts to lowercase:
CREATE TABLE Customer  -- stored the "customer"
SELECT * FROM CUSTOMER -- works (→ customer)

Strings use single quotes. Identifiers use double quotes (only if needed). PostgreSQL normalizes to lowercase — Customer becomes customer. Avoid names with uppercase or reserved words. Convention: everything in snake_case.

-- Never concatenate inputs:
-- "SELECT * FROM users WHERE id = " + input  ← BAD

-- Always use parameters:
SELECT * FROM users WHERE id = $1;

-- Principle of least privilege:
CREATE ROLE app WITH LOGIN PASSWORD 'x';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES
    IN SCHEMA public TO app;
-- No DELETE, no DDL, no superuser

Never concatenate user input — use parameters ($1, ?). It prevents SQL injection. Create roles with least privilege for the application. Do not use superuser for the app. pg_hba.conf controls access by network/IP.

-- Index for a frequent WHERE:
CREATE INDEX idx_active ON customers(active);

-- Composite index (order matters):
CREATE INDEX idx_city_name ON customers(city, name);

-- Partial index:
CREATE INDEX idx_active_ones ON customers(name)
WHERE active = true;

Create indexes for columns in WHERE, JOIN and ORDER BY. Composite indexes follow the column order. A WHERE on the index creates a partial index (smaller, faster). Each index slows down INSERT/UPDATE. Balance reads vs writes.

-- OFFSET (slow for large pages):
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000;

-- Keyset (fast and consistent):
SELECT * FROM posts
WHERE id > 10000
ORDER BY id
LIMIT 20;

OFFSET skips rows (slow for large values). Keyset pagination uses a WHERE with the last seen ID — always fast. Prefer keyset for infinite scroll. OFFSET for pagination with page numbers. Combine with an index on the ordering column.

-- pg_trgm: fuzzy search and fast LIKE
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name ON customers
    USING GIN (name gin_trgm_ops);

-- pg_stat_statements: slow queries
CREATE EXTENSION pg_stat_statements;
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

pg_trgm speeds up LIKE and fuzzy search with a GIN index. pg_stat_statements records all queries with their times. Essential for finding slow queries in production. Enable shared_preload_libraries in postgresql.conf for pg_stat_statements.

PREPARE get_customer (int) AS
    SELECT * FROM customers WHERE id = $1;

EXECUTE get_customer(42);

-- In applications:
-- PHP: $pdo->prepare('SELECT * WHERE id = ?')
-- Node: client.query('SELECT * WHERE id = $1', [id])

PREPARE compiles the query once, executes it many times. $1, $2 are parameters. It prevents SQL injection — never concatenate inputs. The planner can reuse the plan. All languages have native support.

-- IN (materializes the subquery):
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS (stops at the 1st match):
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = customers.id
);

-- NOT EXISTS (safe with NULLs):
WHERE NOT EXISTS (SELECT 1 FROM ...);

EXISTS stops at the first match — faster for large subqueries. IN materializes the whole set. NOT EXISTS is safe with NULLs (NOT IN is not). For small sets, the difference is minimal. The planner can optimize both.

-- Index the result of an expression:
CREATE INDEX idx_email_lower
ON customers (LOWER(email));

-- The query must use the SAME expression:
SELECT * FROM customers
WHERE LOWER(email) = 'anna@example.com';

-- Useful for JSONB:
CREATE INDEX idx_meta ON events ((meta->>'type'));

-- Index over a date cast:
CREATE INDEX idx_day ON sales ((created_at::date));

Expression indexes index the result of a function or expression, such the LOWER(email). The query must use the exact expression for the index to be used. Ideal for case-insensitive searches and JSONB fields. They also work with casts like ::date.

-- CORRECT:
price NUMERIC(10,2)

-- WRONG:
price REAL          -- rounding errors!
price DOUBLE PRECISION

-- Demonstration:
SELECT 0.1::REAL + 0.2::REAL;  -- 0.30000001
SELECT 0.1::NUMERIC + 0.2;     -- 0.3

NUMERIC is exact — essential for monetary values. REAL/DOUBLE have binary representation errors. 0.1 + 0.2 != 0.3 with floats. NUMERIC(10,2) = 10 digits, 2 decimals. Never use float for money.

-- Tables: plural, snake_case
customers, orders, order_items

-- Columns: snake_case
name, created_at, customer_id

-- FKs: singular_table_id
customer_id, product_id

-- Indexes: idx_table_column
idx_customers_email

-- Constraints: pk_, fk_, uq_, chk_

Use snake_case for everything (no uppercase). Tables in the plural (customers). FKs the table_id. Indexes with the idx_ prefix. Constraints with a descriptive prefix. Avoid reserved words (order, group, user).

-- Index only a subset of rows:
CREATE INDEX idx_active_orders
ON orders (customer_id)
WHERE status = 'active';

-- Smaller and faster to maintain
-- than a full index

-- The planner uses it when the
-- query has the same condition:
SELECT * FROM orders
WHERE status = 'active' AND customer_id = 42;

-- Ideal for flags (active/archived)
-- where 90% of rows are "dead"

Partial indexes (CREATE INDEX ... WHERE condition) index only a subset of rows — smaller and faster to maintain. The planner uses them when the query includes the condition. Perfect for flags where most rows are irrelevant.