SQL-i teatmik

SELECT col1, col2 FROM table_name;

Pick columns from a table.

SELECT * FROM t
WHERE col = 5 AND status = 'active';

Filter rows: =, <>, <, >, AND, OR, IN, BETWEEN, LIKE.

SELECT * FROM t ORDER BY created_at DESC;

Sort the result. ASC ascending (default), DESC descending.

SELECT * FROM t ORDER BY id LIMIT 10;

Cap the number of rows returned.

SELECT * FROM a JOIN b ON a.id = b.a_id;

Only rows that have a match in both tables.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

All rows from the left table; NULL on the right if no match.

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

Short names for tables — required when columns share names.

SELECT * FROM sizes CROSS JOIN colors;

Cartesian product — every left row paired with every right row. For generating all combinations.

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

All rows from both tables; NULL where there is no match. MySQL has no FULL JOIN — emulate with LEFT ∪ RIGHT.

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

A table joined to itself via aliases — for hierarchies like «employee → manager».

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Left rows with NO match on the right — «users without orders». An alternative to NOT EXISTS.

SELECT COUNT(*), COUNT(email) FROM users;

Counts rows in a group. COUNT(*) — all rows, COUNT(col) — non-NULL only, COUNT(DISTINCT col) — unique values.

SELECT SUM(amount) FROM orders;

Sum of numeric values in a group. NULLs are ignored. Returns NULL (not 0) for an empty group.

SELECT AVG(price) FROM products;

Arithmetic mean. NULLs are excluded from the divisor. Cast an integer column to numeric or the fractional part is truncated.

SELECT MIN(created_at) FROM orders;

Smallest value in a group. Works with numbers, dates and strings. NULLs are ignored.

SELECT MAX(created_at) FROM orders;

Largest value in a group. Works with numbers, dates and strings. NULLs are ignored.

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

Bucket rows. Every non-aggregate column in SELECT must be in GROUP BY.

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Filter on aggregates — like WHERE but after GROUP BY.

SELECT DISTINCT country FROM users;

Drop duplicate rows from the result.

COUNT(*) FILTER (WHERE type = 'view') AS views

Conditional aggregate: COUNT/SUM only over rows matching WHERE. Replaces three separate queries with one.

STRING_AGG(name, ', ' ORDER BY created_at)

Concatenate values into a single string with a delimiter. The inner ORDER BY locks down order.

ARRAY_AGG(amount ORDER BY created_at)

Collect values into an array. Handy when an audit row needs the full history in one cell.

GROUP BY GROUPING SETS ((kind), (user_id), ())

Multiple grouping levels in one query — row by row: by kind, by user_id, and a grand total.

GROUP BY ROLLUP (DATE_TRUNC('month', ts))

Same grouping level plus a grand-total row (a single NULL row at the end).

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)

Median / quantile. More outlier-resistant than AVG.

SELECT tag FROM articles, UNNEST(tags) tag

Expand an array into rows — one row per array element.

SELECT COUNT(DISTINCT user_id) FROM events;

Counts distinct values. Pricey on big tables — use APPROX_COUNT_DISTINCT / HLL when an estimate is enough.

SELECT BOOL_AND(active), BOOL_OR(is_admin) FROM users;

Boolean aggregates: BOOL_AND is true if EVERY row is true; BOOL_OR if at least one is. NULLs are ignored.

SELECT dept, EVERY(salary > 0) FROM emp GROUP BY dept;

The SQL-standard synonym for BOOL_AND — true when the condition holds for every row in the group.

SELECT STDDEV_SAMP(amount), STDDEV_POP(amount) FROM orders;

Standard deviation: _SAMP for a sample (n-1 divisor), _POP for the full population (n divisor). Bare STDDEV equals STDDEV_SAMP.

SELECT VAR_SAMP(amount), VAR_POP(amount) FROM orders;

Variance — the square of the standard deviation. _SAMP for a sample, _POP for a population. Bare VARIANCE equals VAR_SAMP.

SELECT MODE() WITHIN GROUP (ORDER BY status) FROM tickets;

The mode — the most frequent value in the group. On ties the first by ORDER BY wins.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;

Discrete percentile — returns an actual value from the data, unlike PERCENTILE_CONT which interpolates.

SELECT BIT_OR(flags), BIT_AND(flags) FROM permissions;

Bitwise aggregates over an integer column: BIT_OR collects every set bit, BIT_AND keeps bits common to all rows. For flag masks.

SELECT JSONB_AGG(t ORDER BY t.id) FROM tasks t;

Roll rows up into a JSON array — handy for returning nested data in one query. JSONB_AGG stores it as jsonb (faster, dedups keys).

SELECT JSONB_OBJECT_AGG(key, value) FROM settings;

Fold key-value pairs into a single JSON object. Perfect for turning a settings table into a map.

SELECT CORR(price, sales) FROM products;

Pearson correlation coefficient between two columns: -1 to 1. A measure of linear association.

SELECT REGR_SLOPE(y, x), REGR_INTERCEPT(y, x) FROM points;

Slope and intercept of the least-squares line of y on x — a trend in one aggregate, no external stats package.

SELECT REGR_R2(y, x) FROM points;

The R² coefficient of determination for the regression of y on x: 0..1, how well the line fits the data.

SELECT user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit')  AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdraw') AS withdraw
FROM tx GROUP BY user_id;

Pivot rows into columns: MAX/SUM with FILTER per category. Replaces a hand-written stack of CASE aggregates.

SELECT region, product, SUM(amount)
FROM sales
GROUP BY CUBE (region, product);

Every grouping combination at once: by region, by product, by both, and a grand total. For cross-tab reports.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Test membership against a list produced by another query.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Keep the row if the inner query finds at least one matching row.

SELECT
  name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

A subquery returning one value — can sit inside SELECT.

SELECT id FROM a
UNION ALL
SELECT id FROM b;

Stack the results of two queries (same column count, compatible types). UNION removes duplicates, UNION ALL keeps them (and is faster).

SELECT user_id FROM purchases
INTERSECT
SELECT user_id FROM refunds;

Rows present in BOTH queries. In MySQL — since 8.0.31.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Rows from the first query that are NOT in the second. In Oracle this is MINUS.

SELECT
  name,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM players;

Unique sequential number per row in the window.

RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

Rank with gaps (RANK) or without (DENSE_RANK) on ties.

SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)

Split the window into groups — the aggregate is computed per group.

LAG(price, 1) OVER (ORDER BY date)

Value from the previous (LAG) or next (LEAD) row in the window.

NTILE(4) OVER (ORDER BY score DESC)

Split rows into N equal-sized buckets in order. With uneven counts buckets become 3-3-2-2 (extras go to lower-numbered ones).

LAST_VALUE(score) OVER (
  PARTITION BY team_id ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

First / last value in the window. For LAST_VALUE you must widen the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — the default frame chops the right edge.

PERCENT_RANK() OVER (ORDER BY score DESC, player_id)

Percentile rank in 0..1. A second sort column makes the result deterministic on ties.

NTH_VALUE(amount, 2) OVER (
  PARTITION BY customer_id ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

N-th value from the window. Also needs the widened frame.

AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Rolling windows: «trailing 7 days», «3-day average», etc.

WITH active AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active WHERE country = 'RU';

A named temporary result — break a big query into steps.

WITH a AS (...), b AS (...)
SELECT * FROM a JOIN b ON ...;

Multiple CTEs separated by commas, read top-to-bottom.

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

Walk a hierarchy: anchor query → UNION ALL → recursive step. Perfect for org charts, graphs, chains.

FROM customers c
LEFT JOIN LATERAL (
  SELECT * FROM orders WHERE customer_id = c.id
  ORDER BY amount DESC LIMIT 2
) l ON true

Sub-query sees the outer row's columns. Great for «top-N per X». LEFT JOIN LATERAL … ON true keeps outer rows with no match; the comma form silently drops them.

generate_series('2024-01-01'::date, '2024-01-15'::date, '1 day')

Generate a calendar / axis. Inclusive on both ends. Standard trick for «fill missing days with zero».

INSERT INTO t (col1, col2) VALUES (1, 'a'), (2, 'b');

Add rows to a table.

UPDATE t SET col = 'x' WHERE id = 5;

Modify existing rows. Always include WHERE — otherwise all rows update.

DELETE FROM t WHERE id = 5;

Delete rows. Always include WHERE.

INSERT INTO t (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

Idempotent insert — re-running silently skips rows that already exist.

INSERT INTO t (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE
  SET n = t.n + EXCLUDED.n;

UPSERT: insert or update. EXCLUDED.col is the value we tried to insert.

MERGE INTO t USING src ON t.id = src.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Postgres 15+ — UPSERT alternative with MATCHED / NOT MATCHED branches and per-branch conditions.

INSERT INTO t (name) VALUES ('a')
RETURNING id, name;

Return rows just inserted / updated / deleted in the same statement — no second round-trip.

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.country = 'US';

JOIN-style DELETE — filter by another table without a subquery.

UPDATE customers c SET total = s.total
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) s
WHERE c.id = s.customer_id;

Bulk UPDATE driven by an aggregate subquery.

WITH moved AS (
  DELETE FROM orders WHERE old RETURNING *
)
INSERT INTO archive SELECT * FROM moved;

Atomic archival: move rows in one statement, no race window between DELETE and INSERT.

CREATE TABLE t (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Create a table with typed columns.

ALTER TABLE t ADD COLUMN created_at TIMESTAMP;

Modify an existing table.

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

Reject invalid values at the DB level, not in code.

FOREIGN KEY (post_id) REFERENCES posts(id)
  ON DELETE CASCADE   -- or SET NULL / RESTRICT

What happens to the child row when the parent is deleted: cascade, null out the FK, or block.

ALTER TABLE t
  ADD CONSTRAINT fk REFERENCES p(id) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT fk;

Add a FK to a big production table without a heavy lock: NOT VALID is instant, VALIDATE doesn't block writers.

total NUMERIC GENERATED ALWAYS AS (price * (1 + tax)) STORED

Column value is computed automatically — the formula lives in one place.

CREATE UNIQUE INDEX u ON users (email)
WHERE deleted_at IS NULL;

Uniqueness only over live rows — for soft-delete, so users can re-register after deletion.

CREATE TABLE logs (...) PARTITION BY RANGE (ts);
CREATE TABLE logs_2024 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Slice a big table by range. Old partitions drop in milliseconds.

CREATE TRIGGER touch BEFORE UPDATE ON notes
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

DB-level auto-logic — e.g. set updated_at without touching application code.

CREATE MATERIALIZED VIEW v AS SELECT ...;
REFRESH MATERIALIZED VIEW v;

Cached result of a heavy query. Refresh on a schedule.

LOWER(name), UPPER(code), LENGTH(text)

Lowercase, uppercase, string length.

CONCAT(first_name, ' ', last_name)

Concatenate strings. PostgreSQL also accepts the || operator (in MySQL, || is logical OR by default, not concatenation).

EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)

Pull a part of a date — year, month, day.

DATE_TRUNC('month', created_at)

Round a timestamp down to a period (day/week/month). The go-to tool for grouping by time.

WHERE created_at >= NOW() - INTERVAL '7 days'

Current instant (NOW()) / today (CURRENT_DATE) and interval math — «in the last 7 days».

CAST(price AS INTEGER)   -- or price::int

Convert a value to another type. CAST(x AS type) is standard; x::type is PostgreSQL shorthand.

TRIM(name), SUBSTRING(code FROM 1 FOR 3), REPLACE(phone, '-', '')

Strip whitespace, slice a substring, replace a fragment. Everyday string cleanup.

SPLIT_PART(email, '@', 2)   -- domain from an e-mail

Split a string on a delimiter and take the N-th part. In MySQL — SUBSTRING_INDEX.

WHERE name ILIKE '%ivan%'

Case-insensitive LIKE (PostgreSQL). In MySQL, plain LIKE is already case-insensitive under the default collation.

LEFT(code, 3), RIGHT(phone, 4)

Take the first N characters (LEFT) or the last N (RIGHT) of a string.

POSITION('@' IN email), STRPOS(email, '@')

Index of the first match (1-based), 0 if not found. STRPOS is the PostgreSQL shorthand.

LPAD(id::text, 6, '0'), RPAD(name, 20, ' ')

Pad a string to a target length on the left (LPAD) or right (RPAD). Classic use: zero-pad an id.

INITCAP('john DOE')   -- John Doe

Uppercase the first letter of each word, lowercase the rest. Absent in MySQL.

REPEAT('ab', 3)   -- ababab

Repeat a string N times. Handy for placeholders and simple text bar charts.

REVERSE(name)

Reverse a string character by character. Sometimes used to index by suffix.

char_length(name), char_length('açai')   -- 4

String length in CHARACTERS (not bytes) — matters for UTF-8. Synonym of character_length.

REGEXP_REPLACE(phone, '[^0-9]', '', 'g')

Replace by regular expression. The 'g' flag replaces every match; without it only the first.

SELECT (REGEXP_MATCHES(url, '/(\d+)'))[1] AS id;

Return captured regex groups as an array. With the 'g' flag it yields one row per match.

REGEXP_SPLIT_TO_ARRAY('a, b,c', '\s*,\s*')

Split a string by a regex delimiter into an array. There is also …TO_TABLE for rows.

TRANSLATE(code, 'abc', 'xyz')   -- a->x, b->y, c->z

Character-by-character mapping between two sets. Extra chars in the first set are deleted. Not REPLACE.

BTRIM(code, '0'), LTRIM(s), RTRIM(s, '/')

Strip given chars from both ends (BTRIM), the left (LTRIM) or the right (RTRIM); spaces by default.

FORMAT('Hi %s, id=%L', name, id)

Build a string from a template: %s value, %I identifier, %L safe literal. Key for dynamic SQL.

WHERE STARTS_WITH(path, '/api/')

Whether a string begins with a prefix — clearer than LIKE 'x%'. Available since PostgreSQL 11.

ascii('A')   -- 65
chr(65)      -- A

Code of the first character (ascii) and the character for a code (chr). In MySQL the inverse is CHAR.

to_hex(255)   -- 'ff'

Convert an integer to its hexadecimal string. Handy for colors, bit masks, debugging.

ROUND(3.14159)        -- 3
ROUND(2.5)            -- banker? no: 3

Round to the nearest integer. Halves round away from zero (2.5 → 3).

ROUND(3.14159, 2)     -- 3.14
ROUND(12345.6, -2)    -- 12300

Round to n decimal places; a negative n rounds left of the point. Only works on numeric, not float.

CEIL(4.1)   -- 5
CEIL(-4.1)  -- -4

Round up to the next integer. CEILING is a synonym.

FLOOR(4.9)   -- 4
FLOOR(-4.1)  -- -5

Round down to the previous integer. For negatives it goes further from zero.

TRUNC(3.99)     -- 3
TRUNC(3.456, 2) -- 3.45

Drop the fractional part (toward zero), no rounding. In MySQL it is TRUNCATE(x, n).

ABS(-7)   -- 7

Absolute value — the magnitude without sign.

MOD(10, 3)   -- 1

Remainder of division. Handy for «every N-th row» and parity; the result's sign follows the dividend.

POWER(2, 10)   -- 1024

Raise to a power. POW is a synonym.

SQRT(144)   -- 12

Square root. A negative argument raises an error.

EXP(1)    -- 2.7182818...
LN(2.718) -- ~1

Exponential e^x and natural logarithm (base e). LN(0) and LN(negative) error out.

LOG(100)     -- 2  (base 10)
LOG(2, 8)    -- 3  (base 2)

In PostgreSQL LOG(x) is base 10, LOG(b, x) is an arbitrary base. Caution: in MySQL LOG(x) is the natural log.

SIGN(-42)  -- -1
SIGN(0)    -- 0

Sign of a number: -1, 0, or 1. Handy to branch on direction of change.

GREATEST(a, b, c), LEAST(a, b, c)

Largest / smallest among the arguments within one row (not an aggregate). NULL arguments are ignored.

SELECT * FROM t ORDER BY RANDOM() LIMIT 5;

Random number in [0,1). In MySQL it is RAND(). ORDER BY RANDOM() gives a random sample but is costly on big tables.

DIV(7, 2)   -- 3
7 / 2       -- 3 when both are int

Integer division discarding the remainder. In PostgreSQL / already floors when both operands are ints; MySQL uses the DIV operator for this.

WIDTH_BUCKET(score, 0, 100, 10)  -- bucket 1..10

Assign a value to an equal-width histogram bucket between two bounds. For distributions and range bucketing.

AGE(end_ts, start_ts)   -- or AGE(birthday) vs now
AGE('2024-03-01', '2024-01-15')

Difference between two dates as an interval (years/months/days), not seconds. With one argument it counts from today — handy for age.

DATE_PART('hour', created_at), DATE_PART('dow', created_at)

Function form of EXTRACT — pulls a date/time part as a number. The field is a string, so it's easy to pass dynamically.

EXTRACT(EPOCH FROM (ended_at - started_at)) AS seconds

Turn an interval or timestamp into seconds (Unix time). The go-to way to measure a duration in seconds — then divide by 60/3600.

TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI'), TO_CHAR(amount, 'FM999G999D00')

Format a date/number into a string via a pattern (YYYY, MM, DD, HH24...). PostgreSQL patterns differ from MySQL's DATE_FORMAT.

TO_DATE('2024-03-15', 'YYYY-MM-DD')

Parse a string into a date with an explicit pattern. Safer than a ::date cast when the format is non-standard.

TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
TO_TIMESTAMP(1710512400)   -- from Unix epoch

Parse a string into a timestamp by pattern, or build a timestamptz from Unix seconds (numeric argument).

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP;

Transaction-start time: CURRENT_TIMESTAMP is timezone-aware (timestamptz), LOCALTIMESTAMP is not. Constant within a single transaction.

SELECT CURRENT_DATE, CURRENT_TIME;

Today's date only (CURRENT_DATE) or time only (CURRENT_TIME) — no parentheses; these are SQL special values, not functions.

SELECT order_date + 7, due_date - 1, end_dt - start_dt AS days;

You can add/subtract whole days to a date (date + 7). Subtracting two dates yields an integer day count; two timestamps yield an interval.

make_date(2024, 3, 15), make_time(14, 30, 0)

Build a date or time from separate year/month/day, hour/minute/second numbers — no format-string juggling.

make_timestamp(2024, 3, 15, 14, 30, 0)
make_interval(days => 10, hours => 2)

Build a timestamp or interval from numeric parts. make_interval takes named arguments (days =>, hours =>).

ts_utc AT TIME ZONE 'Europe/Moscow'
local_ts AT TIME ZONE 'UTC'

Shift an instant to another time zone. On a timestamptz it yields local wall time in that zone; on a naive timestamp it interprets it as being in that zone.

JUSTIFY_HOURS(INTERVAL '36 hours')   -- 1 day 12:00:00

Normalize an interval: roll excess hours into days, days into months. Turns «50 hours» into a readable «2 days 02:00:00».

DATE_BIN('15 minutes', ts, TIMESTAMP '2024-01-01')

Snap a timestamp down to the start of an arbitrary-width bucket (e.g. 15 minutes) from an origin. More flexible than DATE_TRUNC. Postgres 14+.

(start_a, end_a) OVERLAPS (start_b, end_b)

Test whether two time periods overlap. Handy for finding booking or shift conflicts.

now()::timestamptz, '2024-03-15 10:00'::timestamp

timestamptz stores the instant in UTC and applies the zone on display; timestamp is naive wall time. For events you almost always want timestamptz.

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 70 THEN 'B'
  ELSE 'C'
END

Inline conditional logic — like if/else inside SELECT.

COALESCE(nickname, full_name, 'Anonymous')

Return the first non-NULL value in the list.

NULLIF(divisor, 0)

Turn a value into NULL when it equals the second argument. Useful to avoid divide-by-zero.

-- = NULL is never true — use IS NULL
WHERE deleted_at IS NULL
-- NULL-safe equality:
WHERE a IS DISTINCT FROM b

Comparing to NULL with = is always «unknown» (not TRUE/FALSE). Use IS NULL to test; use IS DISTINCT FROM for NULL-safe equality.

payload->>'target'

Pull a value from JSONB as text — for string ops and comparisons.

payload @> '{"plan":"pro"}'

Does the JSONB contain the given fragment. Uses GIN — fast on big tables.

CREATE INDEX idx ON events USING GIN (payload jsonb_path_ops)

Optimal index for @> queries on JSONB. Smaller than the default jsonb_ops.

data->'user'->>'name'   -- -> keeps json, ->> as text

-> pulls a field/element as jsonb (to keep drilling), ->> as text. Key by string, array index by number.

data #>> '{address,city}'   -- text at a nested path

Read a value at a nested path given as a key array: #> as jsonb, #>> as text. Shorter than chaining ->.

jsonb_build_object('id', id, 'name', name)

Build a JSON object from alternating key, value pairs. Value types are preserved (numbers stay numbers).

jsonb_build_array(id, name, created_at)

Build a JSON array from the given arguments of any types.

jsonb_agg(item ORDER BY created_at)

Aggregate: collect a group of rows into a JSON array. The inner ORDER BY locks element order.

SELECT e FROM t, jsonb_array_elements(t.tags) AS e

Expand a JSON array into rows — one row per element. The _text variant returns text instead of jsonb.

jsonb_array_length(data->'items')

Length of a JSON array. Errors if the value is not an array — guard with jsonb_typeof.

jsonb_set(data, '{address,city}', '"Lima"')

Return a copy of the JSON with the value at a path replaced. create_missing=true (default) adds the key if absent.

SELECT key, value FROM jsonb_each(data)

Expand a JSON object into (key, value) rows — one per key. The _text variant returns value as text.

SELECT jsonb_object_keys(data)

Return the top-level key names of a JSON object, one row per key.

data ? 'email'        -- has key?
data ?| array['a','b'] -- any of these keys?

Key-existence tests: ? single key, ?| any of these, ?& all of these. Top-level only; GIN-indexable.

data || '{"verified":true}'

Merge two JSONB values: right-hand keys overwrite left (shallow, non-recursive). Handy for a partial update.

data - 'temp'              -- drop a key
data #- '{address,zip}'    -- drop at a path

Delete a key/element: - by top-level key or index, #- at a nested path. Returns a new JSONB.

to_jsonb(row_var)   -- whole row as a json object

Turn any SQL value/row/array into jsonb. A whole table row becomes a JSON object column → value.

jsonb_typeof(data->'price')   -- 'number','string',...

The JSON value's type as text: object, array, string, number, boolean, null. Useful to guard jsonb_array_length etc.

jsonb_pretty(data)

Pretty-print JSONB with indentation for readable output/debugging.

CREATE INDEX i ON orders (id) WHERE status = 'pending';

Index only over the «hot» subset of rows — smaller and faster to scan.

CREATE INDEX i ON orders (customer_id, created_at DESC);

Covers filter + sort in one read. Column order matters.

-- bad : WHERE EXTRACT(YEAR FROM ts) = 2024
-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'

Don't wrap the column in a function — the index won't fire. Rewrite as a range.

-- NOT IN collapses to 0 rows on any NULL
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT IN silently breaks on any NULL in the subquery. NOT EXISTS is NULL-safe.

CREATE INDEX CONCURRENTLY i ON events (user_id, kind);

Build an index on a hot table without a heavy lock. Forbidden inside a transaction.

EXPLAIN SELECT * FROM orders WHERE user_id = 5;

Show the query plan without running it: which scans (Seq Scan / Index Scan), join order, estimated rows.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;

Actually run the query and show REAL time and row counts vs the estimate. A big estimate↔actual gap signals stale stats or a missing index.

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

Lock rows until the end of the transaction. Standard for money transfers.

UPDATE accounts SET balance = balance - 200
WHERE id = 1 AND balance >= 200;

Check-and-update in one atomic statement. If 0 rows updated — surface «insufficient funds».

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 1;

Worker queue: each worker grabs its own job, skipping rows locked by others.

UPDATE counters SET n = n + 1 WHERE id = 1;

A single UPDATE increments the counter race-safely. SELECT-then-UPDATE loses increments.

GRANT SELECT, INSERT ON orders TO analyst;

Give privileges on an object to a role/user. List the actions needed (SELECT, INSERT, UPDATE, DELETE).

REVOKE INSERT ON orders FROM analyst;

Take back privileges previously granted.

CREATE ROLE analyst LOGIN PASSWORD 'secret';

Create a role (user/group). Privileges are granted to the role; users are members of it.

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;

The standard read-only pattern: schema access + SELECT on all tables + a rule for future tables via ALTER DEFAULT PRIVILEGES.