Everything the adaptive question bank can teach and test in SQL, from foundations through advanced practice. Work through it in order, or start practicing and let the questions find your level.
Rows come back in whatever order the chosen plan happens to produce them. In PostgreSQL 17 an UPDATE writes a new row version, so after updating row 1 of a three-row table, a plain SELECT * returned it last. Insertion order is not a contract either. If the order matters to you, it belongs in an ORDER BY on that query, every time you run it.
A primary key promises its columns are unique and never NULL, so one key value names one row for as long as the row exists. Physical position makes no such promise: in PostgreSQL 17 a row's hidden ctid moved from (0,1) to (0,4) after a single UPDATE, and VACUUM FULL can move it again by rewriting the table. Never store a row number or ctid as a reference; store the key.
A table without a primary key can hold two identical rows, and a query returns every copy until DISTINCT, UNION or GROUP BY removes them. The trap shows up during cleanup: with rows (1), (1), (2), DELETE ... WHERE x = 1 removed both copies in PostgreSQL 17, because nothing distinguishes one from the other. A key is what lets you address a single row.
A query describes which rows and columns you want; the planner picks the join order, the join method and whether to use an index, guided by table statistics. Run EXPLAIN to see its choice. The same text can get a different plan after the data grows or ANALYZE refreshes statistics, which is how a query that was fast in testing turns slow without a single edit.
The order is logical, not physical, and several rules fall out of it. WHERE runs before grouping, so it cannot use an aggregate. Window functions run after HAVING. DISTINCT applies after the SELECT list and ORDER BY after that, which is why ORDER BY may use a SELECT alias but WHERE may not. The planner may execute steps in any order that gives the same result.
Each SELECT-list item is an expression evaluated once per surviving row: a column, arithmetic, a function call, or *, which expands to every column in table order. PostgreSQL 17 and SQLite 3.51 both accept a SELECT with no FROM at all, such as SELECT 1+1. An unnamed expression gets a made-up label (?column? in PostgreSQL), so name computed columns with AS.
WHERE filters rows before the SELECT list is computed, so an alias does not exist yet when WHERE is checked; PostgreSQL 17 reports column "d" does not exist. Repeat the expression, or compute it in a subquery and filter in the outer query. SQLite 3.51 accepts an alias in WHERE as an extension, so a query that runs there can fail when you port it.
A comparison involving NULL yields UNKNOWN, and the logic tables follow from that: NULL AND FALSE is FALSE, NULL OR TRUE is TRUE, and NOT NULL stays UNKNOWN. WHERE keeps a row only when its condition is TRUE, so UNKNOWN rows vanish. A CHECK constraint does the opposite and passes on UNKNOWN, so CHECK (x > 0) still admits a NULL in PostgreSQL 17.
PostgreSQL folds an unquoted name to lowercase (the SQL standard says uppercase), so Users and users name one table. Create it as "Users" and you must quote it that way forever; SELECT * FROM users then fails. SQLite 3.51 matches ASCII names case-insensitively even when quoted. In PostgreSQL, single quotes always make a string literal, never a name.
PostgreSQL resolves an unqualified name by walking search_path, which defaults to "$user", public, and takes the first schema holding a match; pg_catalog is searched before them unless the path lists it. A same-named table in an earlier schema silently shadows the one you meant, and CREATE writes into the first schema that exists. Writing public.orders removes the guesswork.
DDL (CREATE, ALTER, DROP) changes structure, DML (INSERT, UPDATE, DELETE) changes rows, and a plain SELECT changes neither, though SELECT ... FOR UPDATE still locks the rows it reads. In PostgreSQL 17, DDL is transactional: BEGIN; CREATE TABLE t (x int); ROLLBACK; leaves no table behind, so a migration can fail without leaving half its structure applied.
B. NULL and the three-valued logic that follows
x = NULL evaluates to NULL for every row, so a WHERE built on it returns nothing and raises no error. PostgreSQL 17's transform_null_equals setting, off by default, exists only for old clients that relied on the wrong form. For a comparison that treats two NULLs as equal, PostgreSQL offers IS NOT DISTINCT FROM and SQLite 3.51 offers its IS operator.
NULL means unknown, so NULL + 1 is unknown too. The danger is silent data loss: a price * qty total becomes NULL for any row missing a quantity, and a later SUM skips that row without warning. Concatenation with || behaves the same way in PostgreSQL 17 and SQLite 3.51, while PostgreSQL's concat() function skips NULL arguments instead.
Because NULLs are skipped, AVG divides by the count of non-NULL values: over 10, NULL and 20 it returns 15, not 10. If a missing value should count as zero, write AVG(COALESCE(x, 0)). PostgreSQL 17 has an exception worth knowing: array_agg and json_agg keep NULLs as elements, while string_agg drops them.
COUNT(*) counts rows, while COUNT(col) counts only rows where col is not NULL. The difference bites after a LEFT JOIN: a parent with no children still produces one NULL-padded row, so COUNT(*) reports 1 for it while COUNT(child.id) reports the correct 0. Count a column from the optional side whenever you mean the number of matches.
x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL, and the second test is UNKNOWN, so no row can come out TRUE. In PostgreSQL 17 the same three rows gave 2 results for NOT IN (1) and 0 for NOT IN (1, NULL). A subquery feeding NOT IN needs only one NULL to do this, while NOT EXISTS is immune to it.
COALESCE returns its first non-NULL argument and evaluates no further, so COALESCE(NULL, 2, 1/0) returned 2 with no error in PostgreSQL 17. Its arguments must share a type, so COALESCE(int_col, 'none') fails. NULLIF(a, b) returns NULL when a equals b, which makes total / NULLIF(n, 0) yield NULL instead of a division-by-zero error.
PostgreSQL 17 sorts NULLs as if larger than every value: last in ascending order, first in descending. SQLite 3.51 treats them as smaller, so they come first ascending and last descending. Both accept explicit NULLS FIRST or NULLS LAST, and writing it out is the only way a 'top 10 by score DESC' query shows the same rows on both engines.
A UNIQUE constraint compares with equality, and NULL never equals NULL, so PostgreSQL 17 and SQLite 3.51 both accepted two NULL rows in a UNIQUE column. PostgreSQL 17 also offers UNIQUE NULLS NOT DISTINCT, which treats NULLs as equal and rejected the second NULL with a duplicate-key error. If the column must always hold a value, NOT NULL is the real fix.
GROUP BY and DISTINCT treat NULLs as not distinct, so every NULL key lands in one group: three rows holding 1, NULL and NULL form two groups. A join condition uses = instead, so a.k = b.k never matches two NULL keys. Data that lines up neatly in a GROUP BY can still drop out of a join on the same column.
In PostgreSQL 17 and SQLite 3.51, '' is a real value of length 0, so '' IS NULL is false and COUNT(col) counts it. Oracle Database treats a zero-length character value as NULL, and its release 23 documentation warns that may change and advises against relying on it. NULLIF(col, '') turns empty strings into NULL; COALESCE(col, '') goes the other way.
C. Filtering with WHERE
PostgreSQL rewrites != to <> while parsing, so the two can never behave differently. Comparisons do not chain: PostgreSQL 17 rejects 1 < 2 < 3 as a syntax error, while SQLite 3.51 reads it as (1 < 2) < 3, compares a 0 or 1, and even returns true for 3 < 2 < 1. Use BETWEEN or two tests joined by AND. SQLite's == is not valid in PostgreSQL.
NOT binds tighter than AND, and AND tighter than OR, so status = 'open' OR status = 'pending' AND owner = 5 reads as open, or pending-and-owned-by-5. On a four-row test it returned both open tickets, including one owned by user 7. Parenthesize the OR group, (status = 'open' OR status = 'pending') AND owner = 5, or write status IN ('open', 'pending').
a BETWEEN x AND y means a >= x AND a <= y, so the bounds must be in ascending order: 5 BETWEEN 10 AND 1 is false, while PostgreSQL's BETWEEN SYMMETRIC swaps them for you. The classic bug is timestamps: BETWEEN '2024-01-01' AND '2024-01-31' stops at midnight on the 31st, so a 09:00 event that day is excluded. Use >= start AND < next_day instead.
IN asks whether a value appears in a list or a one-column subquery; EXISTS asks whether a correlated subquery returns any row at all. For the same semi-join, PostgreSQL 17 produced an identical plan for both, so pick the clearer one: IN for a literal list or a single key, EXISTS when a match needs several columns or extra conditions. Their NOT forms differ once NULLs appear.
% matches any run of characters, including none, and _ matches exactly one. The pattern must cover the whole string, so 'abc' LIKE 'b' is false. To match a literal % or _, escape it: PostgreSQL 17 uses backslash by default, while SQLite 3.51 has no default escape, so 'a_b' LIKE 'a\_b' is false there unless you add ESCAPE '\'.
ILIKE is a PostgreSQL extension that the SQL standard and SQLite lack; SQLite 3.51 rejects it as a syntax error. SQLite's plain LIKE is already case-insensitive for ASCII letters, so the same query can match more rows there than in PostgreSQL. The portable form is lower(col) LIKE lower(pattern), and in PostgreSQL an expression index on lower(col) lets an equality test on it use an index.
In PostgreSQL, ~ is a case-sensitive POSIX regex match, ~* ignores case, and !~ negates. Unlike LIKE, a regex matches anywhere unless anchored, so 'abc' ~ 'b' is true. SIMILAR TO sits between the two: anchored like LIKE but with alternation, so 'abc' SIMILAR TO '%(b|x)%' is true. SQLite's REGEXP operator works only once the application defines a regexp() function.
A B-tree index stores the raw column values, so a test on lower(email), created_at::date or id + 1 cannot search it; PostgreSQL 17 fell back to a sequential scan for all three. Rewrite the test against the bare column, as created_at >= '2024-02-01' AND created_at < '2024-02-02' did to get an index scan, or build an expression index on exactly the expression you filter by.
PostgreSQL types a quoted literal from context, so int_col = '42' works, while comparing an integer with a value already typed text fails with operator does not exist: integer = text. A bad literal errors too, as in 1 = 'abc'. SQLite 3.51 never errors: an INTEGER column converts '1' before comparing, yet a bare 1 = '1' is false. Cast explicitly so the intent is visible.
Write WHERE is_active or WHERE NOT is_active. Both skip NULL rows: in PostgreSQL 17, with one true, one false and one NULL flag, NOT flag returned only the false row. To include unknowns, write WHERE flag IS NOT TRUE, which returned the false and NULL rows. SQLite 3.51 has no boolean type and stores 1 and 0, so the same tests work on those values.
D. Sorting, paging and DISTINCT
With no direction given, each sort key is ascending. ORDER BY runs after the SELECT list, so it can use an output alias, and only LIMIT, OFFSET and row locking come after it. An ORDER BY inside a subquery or view does not promise the order of the outer query, so put it on the outermost SELECT that returns rows to you.
ORDER BY accepts any expression, such as ORDER BY price * qty DESC, including columns you did not select, unless the query uses DISTINCT, where PostgreSQL 17 requires every sort expression to appear in the SELECT list. An integer such as ORDER BY 2 means the second output column and errors if there is none, so reordering the SELECT list silently changes what it sorts by.
The second key is consulted only among rows that tie on the first, the third only among rows that tie on both, and so on. Each key carries its own direction, so ORDER BY a, b DESC sorts a ascending and only b descending; it is not the same as ORDER BY a DESC, b DESC. Ending the list with a unique column makes the order fully determined.
LIMIT cuts the result after it is produced, and without ORDER BY there is no defined first row, so LIMIT 1 can return a different row on another run. PostgreSQL's planner even takes the LIMIT value into account, so changing it can change the plan and the row order. Paging with different OFFSET values is consistent only under an ORDER BY with a unique tiebreaker.
The server still computes every row it skips: in PostgreSQL 17, ORDER BY id OFFSET 100000 LIMIT 10 read 100,010 index entries to return 10, so each page costs more than the one before. OFFSET also drifts when rows are inserted or deleted between requests, which shows one row twice or skips another entirely.
Remember the sort key of the last row on a page and ask for the rows after it: WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 20. With an index on (created_at, id) every page costs about the same, and concurrent inserts do not shift it. Row-value comparison works in PostgreSQL 17 and SQLite 3.51. The trade-off is that you cannot jump straight to page 50.
SELECT DISTINCT a, b removes rows whose whole selected tuple repeats, not repeats of a alone. Parentheses do not narrow it: SELECT DISTINCT (a), b is the same query, since (a) is just a parenthesized expression. To keep one row per value of a, decide which row wins with GROUP BY, a window function, or PostgreSQL's DISTINCT ON.
Which row counts as first is decided by the ORDER BY keys after the DISTINCT ON ones, so DISTINCT ON (location) ... ORDER BY location, time DESC returns each location's newest report. Given an ORDER BY, PostgreSQL 17 refuses the query unless its leading keys match the DISTINCT ON expressions; with none, the surviving row is unpredictable. SQLite 3.51 has no DISTINCT ON; number the rows with ROW_NUMBER() and keep row 1.
Comparing text goes through a collation. The C collation is plain byte order, so A and B sort before a and b; under the ICU collation en-x-icu, PostgreSQL 17 returned a, A, b, B instead. An index serves an ORDER BY or range test only when it was built with the same collation the query uses, so adding COLLATE to a query can bypass an index.
Rows that tie on every ORDER BY key come back in an implementation-dependent order, and a different plan, a parallel scan or fresh data can reshuffle them. ORDER BY score DESC with three people on 90 points is free to list them differently on the next run. Add a unique column as the final key, as in ORDER BY score DESC, id.
A text B-tree index stores keys in the order the collation library defined when it was built. If an OS or ICU upgrade changes those rules, a query can miss rows that are there and a unique index can accept duplicates. PostgreSQL 17 records collation versions and warns on a mismatch where the provider reports one, but nothing marks the index invalid: REINDEX, then ALTER COLLATION ... REFRESH VERSION.
A collation created with deterministic = false, such as ICU und-u-ks-level2, compares 'abc' and 'ABC' as equal, so a UNIQUE column rejects 'alice' after 'Alice'. The cost is restriction: PostgreSQL 17 refused both LIKE and regular expressions on such values, and version 18 allows LIKE but still not regex, SIMILAR TO or ILIKE. B-tree deduplication is also disabled for these indexes.
E. Aggregation and grouping
After grouping, each output row stands for many input rows, so a bare column has no single value to show, and PostgreSQL 17 refuses the query until that column is grouped or wrapped in an aggregate. SQLite 3.51 accepts the query and takes the value from an arbitrary row of the group, a silent bug waiting to happen. Wrap the column in an aggregate or add it to GROUP BY.
WHERE cannot contain an aggregate, because it decides which rows feed the aggregates; HAVING can, because it runs on finished groups. A plain condition such as city LIKE 'S%' works in either clause, but in WHERE it discards rows before the grouping work instead of after it. Keep HAVING for conditions on aggregates, such as count(*) > 1.
With no GROUP BY the whole input forms one group, so you get exactly one row even from an empty table: PostgreSQL 17 returned a count of 0 with a NULL sum and average. Add GROUP BY x to the same empty table and the result is zero rows, because no group exists. A failing HAVING removes the single row too, and selecting a bare column is an error.
COUNT(DISTINCT col) has to deduplicate before it counts, skips NULLs, and takes one expression in PostgreSQL, so count pairs with COUNT(DISTINCT (a, b)). In PostgreSQL 17 it sorted its input, spilling to disk when memory ran out, and ran without the parallel workers a plain COUNT used on the same table. An index on the column lets it read values already in order.
SUM over zero rows has nothing to add, so it returns NULL, as does SUM over rows that are all NULL; COUNT returns 0 in both cases. Code expecting a number breaks on that NULL, so write COALESCE(SUM(amount), 0) when zero is the right answer. With GROUP BY, a group with no rows never appears at all rather than showing a NULL.
Dividing two integers truncates in both PostgreSQL 17 and SQLite 3.51, so 5 / 2 is 2. A hand-rolled SUM(sal) / COUNT(*) over 10, 20 and 5 therefore returned 11, while PostgreSQL's AVG returned the numeric 11.6666666666666667 and SQLite's AVG returns a real. Cast one operand, as in SUM(sal)::numeric / COUNT(*), or use AVG directly.
MIN and MAX on text use the collation's ordering and compare character by character, so '9' is greater than '10' and a longer string gains nothing. Under PostgreSQL's C collation, MAX over '9', '10', 'apple' and 'Banana' returned 'apple', since lowercase letters sort after uppercase there. Keep numbers in a numeric column, or cast before aggregating.
count(*) FILTER (WHERE sal > 6) feeds only matching rows to that one aggregate and leaves the others in the same SELECT untouched. PostgreSQL 17 and SQLite 3.51 both support it. The CASE version hides a trap: COUNT(CASE WHEN sal > 6 THEN 1 ELSE 0 END) counts every row, because 0 is not NULL, so drop the ELSE and let non-matches become NULL.
string_agg(name, ', ' ORDER BY sal DESC) puts its ORDER BY inside the call; without one, the pieces join in whatever order the rows arrive. PostgreSQL 17's string_agg skips NULLs while array_agg keeps them as elements. SQLite 3.51 provides both group_concat and string_agg, and each accepts the same ORDER BY inside the call.
ROLLUP(dept, name) equals GROUPING SETS ((dept, name), (dept), ()): detail rows, per-department subtotals and a grand total from one query, while CUBE adds every combination. A subtotal row shows NULL in the rolled-up columns, so use GROUPING(dept, name) to tell it apart from real NULL data; in PostgreSQL 17 it returned 1 for a subtotal and 3 for the grand total.
With the primary key in the GROUP BY, every other column of that table has one value per group, so PostgreSQL 17 lets you select them ungrouped. Only a primary key counts: grouping by a UNIQUE column that is not the key still raised the GROUP BY error. A view built on this records the dependency, so PostgreSQL refused to drop the key while the view existed.
F. Joins
An inner join emits one output row for each pair of rows that satisfies the ON condition, so rows without a partner on either side disappear, and a key matching three rows produces three output rows. NULL keys never match, because NULL = NULL is not true. JOIN on its own means INNER JOIN, and a comma list with the same condition in WHERE is equivalent.
LEFT JOIN keeps every row of the left table and fills the right table's columns with NULL where no match exists. RIGHT JOIN is the mirror image, so A LEFT JOIN B and B RIGHT JOIN A return the same rows with the columns in a different order. Most code standardizes on LEFT for readability. SQLite 3.51 supports RIGHT and FULL joins as well.
FULL JOIN keeps unmatched rows from both sides and pads the missing side with NULLs. To list rows unique to either side, keep WHERE a.id IS NULL OR b.id IS NULL, testing columns that are never NULL in their own table, and use COALESCE(a.id, b.id) as one key column. PostgreSQL 17 supports FULL JOIN only on merge-joinable or hash-joinable conditions, such as equality.
A CROSS JOIN pairs every row with every row: 3 customers and 3 orders gave 9 rows, and two 10,000-row tables give 100 million. Deliberately, it generates combinations, such as every product with every size. Accidentally, it is a FROM list missing its join condition, FROM a, b, which the database runs without any complaint.
ON is applied while rows are matched, so a failed condition just produces NULL padding; WHERE runs after the join and discards those padded rows. LEFT JOIN ord o ON o.cust_id = c.id WHERE o.status = 'paid' returned only the one customer with a paid order. Moving the test into ON, ... AND o.status = 'paid', kept all three customers.
Joining a table to itself needs two names for one table, so each copy gets an alias: FROM emp e JOIN emp m ON e.manager_id = m.id pairs each employee with their manager. Without aliases PostgreSQL 17 rejects the query because the table name is specified more than once. Once a table has an alias, its original name can no longer be used elsewhere in that query.
Both find rows with no match, and PostgreSQL 17 planned the two identically as a hash anti join. With LEFT JOIN, the IS NULL test must use a column that cannot be NULL in a real match, such as the joined table's key; testing a nullable column also returns rows that matched but held a NULL. NOT EXISTS has no such pitfall, and unlike NOT IN, a NULL cannot empty it.
EXISTS asks only whether at least one matching row exists, so each outer row appears once however many matches it has. An inner join emits a row per match instead: joining 200,000 rows to a lookup table that listed category 2 twice returned 6,000 rows, while the EXISTS version returned 4,000. Patching the join with DISTINCT fixes the count at extra cost.
Joining orders to their items repeats each order's total once per item. In PostgreSQL 17, a $100 order with three items plus a $70 order with one item summed to 370 instead of 170. Aggregate the many side in a subquery first and join the totals, or sum from the table whose grain matches the number you actually want.
USING (id) joins on the named shared column and outputs it once. NATURAL JOIN joins on every column name the two tables share, so adding a column later changes the join silently: customers and orders that both had id and created_at returned zero rows, because both had to match. With no shared names at all, NATURAL JOIN turns into a cross join.
A LATERAL subquery may reference columns of the FROM items before it, so it is evaluated for each left row. That makes top-N per group direct: LEFT JOIN LATERAL (SELECT ... WHERE o.cust_id = c.id ORDER BY total DESC LIMIT 1) t ON true returns each customer's largest order and still keeps customers who have none. SQLite 3.51 has no LATERAL.
For inner joins PostgreSQL may join tables in any order, whatever order you wrote them in. Outer joins limit that freedom, and FULL JOIN fixes the order completely. Setting join_collapse_limit to 1 makes the planner follow your written JOIN order; its PostgreSQL 17 default of 8 lets it reorder up to eight items, and at 12 or more FROM items (geqo_threshold) a genetic search takes over.
G. Subqueries and CTEs
A subquery used as a value must yield at most one row. PostgreSQL 17 raises an error when one returns two rows, rejects a second column outright, and gives NULL when the subquery returns no rows at all. SQLite 3.51 never errors on extra rows; it silently takes the first one, which hides the bug until the data grows.
A subquery that references an outer column is evaluated for each outer row. In PostgreSQL 17 a correlated scalar subquery in the SELECT list appeared in EXPLAIN as a SubPlan, re-run per row, so 100,000 customers means 100,000 executions unless an index makes each one cheap. Rewriting it as a join to a grouped subquery is often faster.
x = ANY (subquery) equals IN, and x <> ALL (subquery) equals NOT IN, NULL trap included: in PostgreSQL 17, 5 > ALL over (3, NULL) is NULL, not true. Empty sets flip the intuition, since ALL over no rows is true and ANY over no rows is false. PostgreSQL also accepts arrays, as in x = ANY(ARRAY[1,2,3]); SQLite 3.51 has neither keyword.
EXISTS only checks whether the subquery produces a row, so the executor can stop at the first match and never computes the select list. In PostgreSQL 17 even EXISTS (SELECT 1/0 FROM ord) returned true with no division error. Writing SELECT 1 states that intent plainly; SELECT * would not be slower, only less clear to the reader.
A derived table is a parenthesized SELECT in FROM that the outer query treats as a table, so you can filter or join on its computed columns. The SQL standard requires an alias, and PostgreSQL did through version 15; on PostgreSQL 17 and SQLite 3.51, SELECT * FROM (SELECT 1) runs without one. Add an alias anyway, since other engines still demand it.
WITH recent AS (SELECT ...) names a step that the main query then reads like a table. A later CTE in the same WITH may reference an earlier one, and the main query can read one CTE several times. The name exists only for that single statement; the next statement cannot see it, unlike a view or a temporary table.
PostgreSQL 11 and earlier always computed a CTE first, so outer filters could not reach inside it. In PostgreSQL 17 a non-recursive, side-effect-free CTE read once is inlined: WITH w AS (SELECT * FROM big) SELECT * FROM w WHERE id = 5 used the primary key index. Read it twice, or write AS MATERIALIZED, and it ran once as a full scan, ignoring that index.
The anchor runs once; then the recursive term runs repeatedly, each time against only the rows the previous pass produced, and the query stops when a pass produces nothing. So the recursive term needs a condition that eventually fails, such as WHERE n < 100. PostgreSQL 17 rejects an aggregate or a second reference to the CTE inside the recursive term, and a LIMIT attached to the whole recursive UNION.
On a graph where 1 links to 2, 2 to 3 and 3 back to 1, the recursive term never runs out of rows. Carry the visited path, starting with ARRAY[id] and extending it with path || id, and stop when id = ANY(path). PostgreSQL 17 automates this with CYCLE id SET is_cycle USING path, which flagged the return to node 1 and stopped there.
A CTE lives for one statement and is visible only inside it. A temporary table lasts until your session ends, or until the transaction ends with ON COMMIT DROP, is visible only to your session, and can be indexed and reused by many statements. PostgreSQL's autovacuum cannot analyze temporary tables, so run ANALYZE after filling one and before querying it.
H. Window functions
Adding OVER turns an aggregate into a window function: avg(salary) OVER (PARTITION BY dept) puts the department average beside every employee instead of collapsing them into one row per department. Window functions run after WHERE, GROUP BY and HAVING, so they see only the rows those steps left. SQLite 3.51 supports them as well.
PARTITION BY splits the rows into groups that share the listed values, and the function starts fresh in each one, so ROW_NUMBER restarts at 1 for every department. Leaving it out makes the whole result a single partition. Unlike GROUP BY, every input row stays in the output with its own detail columns.
With ORDER BY in OVER, the default frame runs from the partition's first row to the current row's last tie, so sum(score) OVER (ORDER BY score) becomes a running total. Ties share a value: two 90-point rows after an 80 both showed 260 in PostgreSQL 17. Use ROWS UNBOUNDED PRECEDING for a strict row-by-row total, which gave 170 and then 260.
For scores 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4 and breaks the tie in an unspecified order; RANK gives 1, 2, 2, 4, skipping a number after the tie; DENSE_RANK gives 1, 2, 2, 3 with no gap. Choose RANK for competition standings, DENSE_RANK for 'the nth distinct value', and ROW_NUMBER when you need exactly one row per slot.
lag(x) reads the previous row in the window's order and lead(x) the next one, both within the partition and ignoring any frame clause. Rows at the edge get NULL unless you pass a default: lead(score, 1, 0) returned 0 on the last row. PostgreSQL 17 rejects IGNORE NULLS, so skipping over NULL values takes another approach.
These read the frame, not the partition. With ORDER BY and the default frame, the frame ends at the current row's last tie, so last_value(name) returned a name from the current row's own score group, not the final row. Write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the whole partition. nth_value(x, 2) stays NULL until the frame holds two rows.
ROWS counts physical rows, so ROWS 2 PRECEDING always means two rows back. RANGE works on the ORDER BY value: rows tied with the current one enter or leave together, and an offset such as RANGE BETWEEN 10 PRECEDING AND CURRENT ROW covers scores within 10 points, which requires exactly one ORDER BY column. GROUPS counts peer groups instead.
When OVER has an ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ending at the current row's last tie, which is why sums run and last_value looks wrong. With no ORDER BY, every row is a peer of the current row, so the very same default covers the whole partition. Spell the frame out whenever the difference matters.
Exclusion trims rows after the frame is set: CURRENT ROW drops this row only, GROUP drops it with its ties, and TIES drops the ties but keeps it. avg(score) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) gives the average of everyone else, 93.33 for the 80 in a 100-90-90-80 set. The default, EXCLUDE NO OTHERS, removes nothing.
ntile(4) deals rows into four numbered buckets as evenly as it can, and leftovers go to the earliest buckets, so 10 rows split 3, 3, 2, 2. Tied values can land in different buckets. For real percentiles use the ordered-set aggregates: percentile_cont(0.5) WITHIN GROUP (ORDER BY x) interpolates, while percentile_disc returns an actual value from the data.
Use GROUP BY when you want one row per group, such as totals by department. Use a window function when each detail row needs a group-level figure beside it, such as each salary next to its department total, or a rank. The two combine: sum(sum(score)) OVER () puts the grand total on every grouped row, because windows run after grouping.
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC) lets sum(salary) OVER w and avg(salary) OVER w share one definition. A window can extend another only by adding what it lacks: PostgreSQL 17 refused to override an existing ORDER BY and refused to copy a window that already had a frame clause. Put the frame in the referencing OVER instead.
Window functions are computed after WHERE, GROUP BY and HAVING, so WHERE cannot see their results: PostgreSQL 17 rejects WHERE rank() OVER (...) = 1 with window functions are not allowed in WHERE, and an alias fails too. Compute the window in a subquery or CTE, then filter on its column in the outer query. PostgreSQL 17 has no QUALIFY clause.
I. Set operations
UNION removes duplicates across the whole combined result, including repeats inside a single branch, so rows (1), (1), (2) unioned with (3) returned 1, 2, 3. That deduplication needs a sort or hash over every row. UNION ALL just appends and keeps all copies, so it is faster; use it whenever the branches cannot overlap or the duplicates are wanted.
Without ALL, INTERSECT and EXCEPT return each qualifying row once. With ALL they count copies: a row appearing m times on the left and n times on the right appears min(m, n) times in INTERSECT ALL and max(m − n, 0) times in EXCEPT ALL. EXCEPT is not symmetric, so swapping the inputs changes the answer. SQLite 3.51 lacks both ALL forms.
Branches must return the same number of columns, matched by position, never by name, and each position must resolve to one type. PostgreSQL 17 rejects SELECT 1 UNION SELECT 'a'::text with UNION types integer and text cannot be matched. The output column names come from the first branch. SQLite 3.51 accepts mixed types without complaint.
A trailing ORDER BY sorts the combined result, not the last branch. In PostgreSQL 17 it may refer only to output columns, by first-branch name or by position: ORDER BY a + 1 fails, and so does a name used only in the second branch. To sort or limit one branch, wrap it in parentheses, as in (SELECT ... LIMIT 5) UNION ....
UNION stacks the rows of compatible queries, giving more rows with the same columns; a join lines rows up side by side, giving wider rows. INTERSECT also differs from an inner join on every column: it treats two NULLs as matching and returns each row once, while the join drops NULL keys and can repeat rows.
In PostgreSQL, A UNION B INTERSECT C means A UNION (B INTERSECT C), while UNION and EXCEPT associate left to right. SQLite 3.51 gives all three equal precedence, left to right, so SELECT 1 UNION SELECT 2 INTERSECT SELECT 2 returned 1 and 2 in PostgreSQL 17 but only 2 in SQLite. State the grouping yourself, with parentheses or a subquery in FROM.
EXCEPT compares whole rows and treats NULLs as equal, so a NULL on the right just cancels a NULL on the left: (1, 2, NULL) EXCEPT (1, NULL) returned 2. The NOT IN version, x NOT IN (SELECT ...) over the same right side, returned nothing because of that NULL. EXCEPT also removes duplicates, while NOT IN keeps repeated left rows.
Duplicate removal asks whether two rows are distinct, not whether they are equal, so two NULLs count as the same value: SELECT NULL UNION SELECT NULL returns one row, and DISTINCT and GROUP BY collapse NULLs the same way. The same pair compared with = yields NULL, so a join or WHERE test on that column would not match them.
VALUES (1, 'a'), (2, 'b') is a complete query on its own, with columns named column1 and column2 in PostgreSQL 17 and SQLite 3.51. In FROM, wrap it in parentheses, and in PostgreSQL an alias can name the columns: FROM (VALUES (1, 'a')) AS t(id, label). Each column takes one type across all rows, so 1 and 2.5 both became numeric.
With UNION, each pass of a recursive CTE compares its new rows against every row produced so far, which grows costly on deep traversals. It also stops a loop only when repeated rows are identical, and a depth or path column makes every row unique. So UNION ALL is the usual choice, with an explicit stop condition or a CYCLE clause doing the real work.
J. Data types
smallint takes 2 bytes (−32,768 to 32,767), integer 4 bytes (−2,147,483,648 to 2,147,483,647) and bigint 8 bytes. PostgreSQL 17 raises integer out of range instead of wrapping, so 2147483647 + 1 fails while casting one side to bigint works. A serial key is a plain integer and can run out. SQLite 3.51 integers are 64-bit: + past that range yields a float, but sum() raises integer overflow.
numeric stores decimal digits exactly, so 0.1 + 0.2 is exactly 0.3. double precision stores a binary fraction that cannot represent 0.1, so in PostgreSQL 17 0.1::float8 + 0.2::float8 printed 0.30000000000000004, and summing 0.1 ten times gave 0.9999999999999999. Floats are faster and fine for measurements; use numeric when every digit must be exact.
Currency needs exact cents, and binary floats leave residue: in PostgreSQL 17, 0.1::float8 + 0.2::float8 - 0.3::float8 came to about 5.55e-17, so a ledger that should balance fails a = 0 test. Store amounts as numeric(12,2) or as integer cents. PostgreSQL's money type is a poor substitute, since its precision and output follow lc_monetary, and a dump may not load under a different setting.
char(n) pads with spaces and ignores trailing spaces when comparing, varchar(n) rejects a longer string with an error, and text has no declared limit. PostgreSQL 17 has two quirks: input that is too long only because of trailing spaces is truncated silently, and an explicit ::varchar(3) cast truncates any string. Its docs report no real speed difference between them, and SQLite 3.51 enforces no length at all.
PostgreSQL 17 accepts true/false, yes/no, on/off, 1/0, t/f and unique prefixes such as y, ignoring case and surrounding spaces, and psql prints t or f. SQLite 3.51 has no boolean type: the keywords TRUE and FALSE simply mean 1 and 0, and inserting 'yes' into a BOOLEAN column stores the text 'yes', which then fails a = 1 test.
date holds a calendar day, time a time of day with no date, and timestamp both, to microsecond resolution in PostgreSQL 17. Adding an integer to a date adds days, and subtracting two dates gives an integer day count, 29 across February 2024. None of these keeps a time zone; that is what timestamptz is for. SQLite 3.51 has no date types and keeps dates as text or numbers.
timestamptz converts input to UTC and displays it in the session's TimeZone, so one instant reads 07:00+00 in UTC and 02:00-05 in New York. Plain timestamp keeps the digits you typed and silently drops any offset, so '2024-01-01 12:00+05'::timestamp stays 12:00. Neither type stores the zone name. Use timestamptz for events that happened at a real moment.
PostgreSQL 17 keeps an interval's months, days and microseconds separate, because month length varies. Adding a month to January 31 gave February 29, 2024, and subtracting a month from that landed on January 29, not the 31st. Across New York's March daylight-saving change, + interval '1 day' kept noon while + interval '24 hours' gave 13:00; across the November change it gave 11:00.
serial is shorthand for an integer column whose default calls a sequence, and it takes an explicit value without complaint; the standard GENERATED ALWAYS AS IDENTITY refuses one unless you write OVERRIDING SYSTEM VALUE. Sequence values are never rolled back, so a failed insert leaves a gap. Inserting an explicit id leaves the sequence behind: in PostgreSQL 17 the next default insert then hit a duplicate-key error.
A random version 4 UUID, from gen_random_uuid() in PostgreSQL 17, lands at a random spot in the B-tree, so inserts touch scattered pages instead of appending at the end, and each key takes 16 bytes against bigint's 8. Sorting by it says nothing about creation order. PostgreSQL 18 adds uuidv7(), whose time-ordered prefix restores insert locality; 17 lacks it.
An enum is compact and sorts in declaration order, not alphabetically. Changing it is lopsided in PostgreSQL 17: you can add a value with ALTER TYPE ... ADD VALUE or rename one, but dropping a value is not implemented, so removal means building a new type and migrating columns. A lookup table with a foreign key costs a join but lets you add, retire or describe values as ordinary rows.
CAST(x AS integer) is standard, and x::integer is PostgreSQL shorthand for the same thing. Arithmetic promotes on its own, so 1 + 2.5 yields numeric 3.5. A failed cast errors in PostgreSQL 17, as 'abc'::int does, and numeric-to-integer rounds half away from zero, so 2.5 becomes 3. SQLite 3.51 never errors: CAST('abc' AS INTEGER) is 0.
In numeric(5,2), 5 is the total count of significant digits and 2 the digits after the point, so it holds up to 999.99. PostgreSQL 17 rounds extra decimals on write, half away from zero, so 1.005 becomes 1.01. If the rounded value still needs too many digits before the point, as 1234.5 and 999.995 do, the write fails with numeric field overflow instead of truncating.
Binary floats hold most decimals approximately, so 0.1::float8 + 0.2::float8 = 0.3 is false; compare with a tolerance such as abs(a - b) < 1e-9, or use numeric. PostgreSQL 17 departs from IEEE rules for NaN so it can sort and index: NaN equals NaN and sorts above every other value, after Infinity, with NULLs still last in ascending order.
PostgreSQL range types such as int4range, daterange and tstzrange hold both bounds in one value. Discrete ranges are normalized to [), so int4range(1,10,'[]') displays as [1,11). Test with @> for containment and && for overlap. With the btree_gist extension, EXCLUDE USING gist (room WITH =, during WITH &&) rejected an overlapping booking in PostgreSQL 17.
CREATE TYPE money_pair AS (amount numeric, cur text) defines a row type that one column can hold. To read a field, wrap the column in parentheses, (item).amount, because item.amount is parsed as column amount of a table named item; PostgreSQL 17 rejects it with missing FROM-clause entry for table "item". (item).* expands every field into its own column.
K. Expressions and functions
A searched CASE tests a boolean in each WHEN, while a simple CASE compares one operand with =, so CASE x WHEN NULL THEN can never match: NULL equals nothing. Branches are tried top to bottom and the first true one wins, so put the narrow conditions first. With no ELSE and no match, PostgreSQL 18 returns NULL rather than raising an error, which can leak NULLs into a column you thought was fully classified.
In a UTF-8 PostgreSQL 18 database, length counts characters and octet_length counts bytes, so any accented letter makes them differ. Positions start at 1: position('c' in 'abc') is 3, and 0 means not found rather than an error. substring(s from 2 for 3) takes three characters starting at the second. trim strips spaces unless you list other characters. SQLite calls position instr.
In PostgreSQL 18 and SQLite, || returns NULL when either operand is NULL, so one missing middle name blanks the whole full-name column instead of leaving a gap. Wrap the nullable part in coalesce(middle, ''), or use concat(), which treats NULL as an empty string, or concat_ws(' ', ...), which also drops the separator next to a NULL. Non-NULL numbers are cast to text: 'n=' || 5 gives 'n=5'.
date_trunc('month', ts) keeps a timestamp and zeroes every finer field, which makes it the right GROUP BY key for monthly totals. EXTRACT(month FROM ts) returns a bare number, so grouping on it merges March of every year. In PostgreSQL 18, EXTRACT returns numeric, a week truncates to its Monday, and DOW counts Sunday as 0 where ISODOW counts it as 7.
In PostgreSQL 18, now() and CURRENT_TIMESTAMP are fixed at the start of the transaction, so every row one transaction writes gets the same time, by design. statement_timestamp() advances per statement and clock_timestamp() advances even inside one statement. Timing a slow step with now() inside a transaction therefore measures zero; take clock_timestamp() before and after instead.
PostgreSQL 18 rounds a numeric tie away from zero, so round(2.5) is 3 and round(-2.5) is -3, while a double precision tie usually goes to the even neighbor, giving round(2.5::float8) = 2 on most platforms. trunc cuts toward zero, so it agrees with floor on positives and with ceil on negatives: 2.7 gives 2, 2 and 3, and -2.7 gives -2, -3 and -2.
When both operands are integers, / discards the fraction and truncates toward zero, so 7/2 is 3 and -7/2 is -3 in PostgreSQL 18 and SQLite. Make one operand non-integer before dividing, with 7/2.0 or x::numeric / y. Casting the result afterward is too late: (7/2)::numeric is already 3. Dividing by zero raises an error in PostgreSQL but quietly returns NULL in SQLite.
In PostgreSQL 18 and SQLite the remainder takes the sign of the dividend, because the quotient truncates toward zero: -7 % 3 is -1 and 7 % -3 is 1. Python's % follows the divisor instead and gives 2 for the first, so bucketing logic ported from application code breaks on negative input. For a remainder that is never negative, write ((a % n) + n) % n.
MAX and MIN collapse many rows into one; GREATEST and LEAST compare several expressions inside a single row, such as the later of shipped_at and paid_at. PostgreSQL 18 ignores NULL arguments and returns NULL only when every argument is NULL, which its docs flag as a deviation from the standard. SQLite has no GREATEST; its multi-argument max(a, b) returns NULL as soon as any argument is NULL.
Called in FROM, generate_series(start, stop, step) yields one row per value, and stop is included when a step lands on it. LEFT JOIN your data to a series of days and the empty days appear with a zero count instead of vanishing. A step pointing away from stop returns no rows, and a zero step is an error. In PostgreSQL 17 a monthly series from January 31 sticks to February's clipped day: the 28th in 2023, the 29th in 2024.
L. Modifying data
Always name the columns: INSERT INTO t (a, b) VALUES ... keeps working when a column is added, while a bare VALUES list is matched by position and silently fills the wrong column if the order shifts. A multi-row VALUES list is one statement, so if any row violates a constraint, PostgreSQL 17 inserts none of them. INSERT ... SELECT copies a query's result and pairs columns by position, not by name.
RETURNING hands back the affected rows from the same statement, so you get a generated id or a trigger-computed value without a second query that might see someone else's write. In PostgreSQL 18, UPDATE returns new values by default, DELETE returns the removed rows, and old. and new. give both. An ON CONFLICT DO NOTHING that skips a row returns nothing for it. SQLite has RETURNING too, in unspecified row order.
PostgreSQL 18 joins in other tables with UPDATE t SET ... FROM s WHERE t.id = s.id, and SQLite adopted the same form in 3.33.0. The target is not repeated in FROM unless you want a self-join; SQL Server does repeat it, and MySQL writes the join between UPDATE and SET. If one target row joins several source rows, only one is applied and which one is unpredictable, so aggregate the source to one row per key first.
Leave off WHERE and UPDATE rewrites every row, and nothing warns you: the only signal is a row count such as UPDATE 48211 after the fact. Inside BEGIN you can still ROLLBACK; under autocommit the change is already committed. A safer habit is to run the predicate as a SELECT count(*) first, then run the UPDATE inside a transaction and check that its count matches before you COMMIT.
In PostgreSQL 18, DELETE visits and marks each row, fires row triggers and leaves the space for VACUUM; TRUNCATE skips the scan and frees the space at once. The price is that TRUNCATE fires no ON DELETE triggers, takes an ACCESS EXCLUSIVE lock that blocks even readers, and refuses a table other tables reference unless they are truncated too. Both roll back inside a transaction. SQLite has no TRUNCATE statement.
The arbiter is the unique index or constraint whose violation triggers the alternative action. In PostgreSQL 18, DO NOTHING may omit the target and then covers every usable constraint, but DO UPDATE must name one, and it reads the proposed row as excluded. DO UPDATE cannot touch one row twice in a statement, so a batch holding duplicate keys fails. Unlike SELECT-then-INSERT, it stays correct under concurrency.
MERGE joins a source to a target and runs the first WHEN clause that applies to each joined row: update or delete when matched, insert when not. PostgreSQL added it in 15, and 17 added RETURNING and WHEN NOT MATCHED BY SOURCE; SQLite 3.51 has no MERGE. It is not a race-free upsert: if another session inserts the same key concurrently, MERGE raises a unique violation where ON CONFLICT would update.
Each single-row INSERT under autocommit pays a network round trip, a parse and a commit; batching rows into one multi-row VALUES list, or wrapping many inserts in one transaction, pays most of that once. PostgreSQL 18 recommends COPY for large loads as faster still, and for a fresh table, loading before you create indexes and foreign keys, then running ANALYZE so the planner sees the new data.
A default applies only when the column is omitted or you write the keyword DEFAULT; an explicit NULL is stored as NULL even when a default exists. In PostgreSQL 18 the default expression is evaluated for each inserted row, not when the table is created, so DEFAULT now() records the inserting transaction's time. With no declared default the default is NULL, and INSERT INTO t DEFAULT VALUES fills every column.
A generated column is computed from other columns of the same row and cannot be written directly. STORED computes it on insert and update and keeps it on disk; VIRTUAL computes it on each read and stores nothing. PostgreSQL 18 and SQLite pick VIRTUAL when you name neither, while PostgreSQL 17 supports only STORED and rejects DDL that omits the keyword. Both engines refuse a non-deterministic expression such as random().
M. Constraints and integrity
In PostgreSQL 18 a PRIMARY KEY forces its columns NOT NULL and builds a unique B-tree index, and a table gets at most one. UNIQUE plus NOT NULL enforces nearly the same rule, but only the primary key is what a foreign key references when you omit the column list. SQLite is the outlier: unless the column is INTEGER PRIMARY KEY or the table is STRICT or WITHOUT ROWID, it lets NULLs into a primary key.
By default PostgreSQL 18 and SQLite treat NULLs as distinct under UNIQUE, so any number of rows may hold NULL in the column; the rule binds only non-NULL values. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT, which allows a single NULL. In a multi-column UNIQUE, a NULL in any column exempts the row, so (tenant, NULL) can repeat. A rule covering only some rows needs a unique partial index instead.
A foreign key requires each referencing value to exist in a primary key or unique column of the parent, so orphans cannot be inserted. A NULL in any referencing column skips the check unless you declare MATCH FULL. PostgreSQL 18 indexes the parent side but not the child side, so deleting a parent scans the child table until you add that index. SQLite 3.51 enforces none of this until PRAGMA foreign_keys = ON.
When a parent row is deleted, CASCADE deletes its children, SET NULL or SET DEFAULT rewrites their key columns, and NO ACTION or RESTRICT raises an error. In PostgreSQL 18 those two differ in timing: NO ACTION, the default, judges the state after the statement and can be deferred to commit, while RESTRICT refuses as soon as a referencing row exists and never defers. SET DEFAULT fails if the default has no parent.
A CHECK rejects any insert or update that makes its expression false, whether the write comes from the application, a migration script or a console session. In PostgreSQL 18 it may look only at the row being written: a CHECK that calls a function reading other rows seems to work in tests but is not re-checked when those rows change, and can make a dump fail to restore. The expression is assumed immutable.
NOT NULL and CHECK (col IS NOT NULL) reject the same rows, but PostgreSQL 18 documents the dedicated constraint as more efficient, and the planner reads it: on a NOT NULL column, PostgreSQL 17 drops a redundant IS NOT NULL filter and turns IS NULL into a plan that returns no rows. Reach for CHECK when the rule is conditional, such as CHECK (status <> 'shipped' OR shipped_at IS NOT NULL).
A DEFERRABLE constraint can wait until COMMIT, via INITIALLY DEFERRED or SET CONSTRAINTS ... DEFERRED, so rows that are only consistent together can be written in any order. In PostgreSQL 18 only UNIQUE, PRIMARY KEY, EXCLUDE and foreign keys can defer. A plain UNIQUE is checked row by row, so UPDATE t SET pos = pos + 1 can fail midway; DEFERRABLE INITIALLY IMMEDIATE checks at the end of the statement instead.
A composite key's uniqueness ignores column order, but the B-tree index built for it does not: an index on (order_id, line_no) can seek by order_id alone, while a filter on line_no alone cannot seek into it. Put first the column you filter on most, usually the parent id. PostgreSQL 18's skip scan generally pays off only when the leading column has few distinct values. In a join table, add a second index for the reverse direction.
A natural key such as an email or ISBN carries meaning, so it can change, and every child row that copies it must change too; ON UPDATE CASCADE exists for that, at the cost of rewriting those rows. A surrogate key, an identity integer or UUID, never needs to change because it means nothing. The common design uses a surrogate primary key and keeps the natural key under UNIQUE, so the real-world rule is still enforced.
Constraints are facts the planner reasons from. In PostgreSQL 17, a LEFT JOIN on a unique column vanished from the plan when none of its columns were selected, and WHERE col IS NULL on a NOT NULL column became a plan that reads nothing. GROUP BY columns made redundant by a primary key were already dropped, and PostgreSQL 18 extends that to unique indexes. A constraint added NOT VALID is not assumed to hold until validated.
CREATE DOMAIN posint AS int CHECK (VALUE > 0) states the rule once and enforces it in every column of that type and on every cast, so (-5)::posint fails too. As with table checks, NULL passes. PostgreSQL 18 advises against NOT NULL on the domain itself, because an outer join or an empty scalar subquery can still produce a NULL of that type; put NOT NULL on each column instead.
An exclusion constraint generalizes UNIQUE: no two rows may make every listed operator true at once. EXCLUDE USING gist (room WITH =, during WITH &&) rejects overlapping ranges for the same room, while back-to-back half-open bookings pass. The = on a plain integer inside a GiST index needs the btree_gist extension. In PostgreSQL 18 an exclusion constraint cannot serve as an ON CONFLICT DO UPDATE arbiter.
A CHECK passes when its expression is TRUE or NULL, and most comparisons with a NULL operand yield NULL, so CHECK (price > 0) happily stores a NULL price. Add NOT NULL, or spell out the NULL case in the condition. Multi-column rules leak the same way: CHECK (end_date > start_date) accepts any row where either date is missing.
Adding a CHECK or foreign key normally scans the whole table while writers wait. With NOT VALID, PostgreSQL 18 skips that scan, enforces the rule on new and updated rows immediately, and lets VALIDATE CONSTRAINT check the old rows later under a SHARE UPDATE EXCLUSIVE lock that lets writes continue. Version 18 also accepts NOT VALID on not-null constraints; 17 allowed it only for CHECK and foreign keys.
N. Indexes
A B-tree index holds a sorted copy of the indexed values, each paired with the physical location of its row, arranged in a balanced tree. Sorting is what makes it useful: one descent finds an equality match or the start of a range, and walking the leaves in order serves ORDER BY. In PostgreSQL 18 every index is stored apart from the table, so a typical lookup reads the index and then visits the row.
An index pays off when a query keeps a small fraction of the rows; beyond that, hopping between index and table costs more than reading the table front to back. In PostgreSQL 17 on 200,000 rows, a = 5 AND b = 5 used an index while a < 90, matching about 90 percent of rows, chose a sequential scan with the same index available. The choice rests on statistics, so a stale estimate can tip it the wrong way.
A B-tree on (a, b) is sorted by a, and by b only within each a. In PostgreSQL 17, b narrows the scanned range only when a is pinned by equality; after a range on a, a test on b is merely checked inside the index, which still saves table visits, and b alone cannot seek at all. PostgreSQL 18 adds skip scan, which probes each distinct a and pays off only when a has few values.
An index-only scan builds each result row from the index entry itself, so every column the query touches must be in the index. INCLUDE (b) adds b as payload, stored in the entries but not part of the search key or of any unique rule. PostgreSQL still consults the visibility map: after updates, Heap Fetches climbs until VACUUM runs. Selecting one column the index lacks cancels the index-only scan entirely.
CREATE INDEX ... WHERE status = 'pending' indexes only the matching rows, so it stays small when they are a minority, and a partial UNIQUE index can enforce uniqueness among just those rows. The planner uses it only when the query's WHERE provably implies the predicate at planning time; in PostgreSQL 17 a generic prepared plan for status = $1 ignored it and scanned the table.
A plain index on email cannot serve WHERE lower(email) = 'x', because it stores the raw values. CREATE INDEX ON users (lower(email)) stores the computed result, and a query must use the identical expression to match it; an ILIKE still scans. The expression is computed on every insert and every non-HOT update, and PostgreSQL 18 needs an ANALYZE before it has statistics on the new expression.
In PostgreSQL 18 a UNIQUE constraint is enforced by the unique B-tree index it creates, so the duplicate check is identical, and a second index on the same column only duplicates it. The declaration matters elsewhere: only an index can be partial or built on lower(email), and only a constraint can be DEFERRABLE or named in ON CONFLICT ON CONSTRAINT. ADD CONSTRAINT ... UNIQUE USING INDEX promotes an existing index.
Every INSERT adds an entry to each index on the table, and an UPDATE adds new entries to all of them unless it qualifies as a HOT update: no column of a non-BRIN index changed, and the old version's page has room for the new one. Indexing a column you update often therefore takes every such update off the HOT path. Check pg_stat_user_indexes.idx_scan for indexes that are maintained but never read.
GIN is an inverted index with one entry per element, so it answers which rows contain a given word, key or array value, for tsvector, arrays and jsonb; PostgreSQL 18 names it the preferred index for text search. GiST is a framework for overlap and distance, serving ranges, geometry and exclusion constraints. GIN updates are slow because one row inserts many keys; GiST's text search is lossy and rechecks rows.
Deletes and updates leave dead index entries. VACUUM removes them and recycles B-tree pages that become completely empty, but a page keeping a few live keys stays allocated, so a table that deletes most rows in each key range ends up with a sparse, oversized index. REINDEX rebuilds it compactly, but in PostgreSQL 18 it holds ACCESS EXCLUSIVE on the index, which stalls nearly every query on the table; REINDEX CONCURRENTLY avoids that.
A hash index stores a 32-bit hash of each value, not the value itself, so it answers only =: no ranges, no ORDER BY, no multicolumn key and no UNIQUE. Its edge is on long keys such as URLs, where it can be much smaller than a B-tree that must store each whole string. PostgreSQL 10 made hash indexes WAL-logged and crash-safe; old advice to avoid them dates from before that.
BRIN keeps one summary, such as the minimum and maximum, per range of table pages (128 by default), so it is tiny and a scan skips every range that cannot match. That works only when physical order tracks the value, as in an append-only log ordered by created_at. Once rows arrive out of order, each range spans nearly all values and the index excludes almost nothing. Matches are lossy, so rows are rechecked.
An index serves an operator only if the column's operator class includes it. In a database whose collation is not C, a default B-tree on text cannot serve LIKE 'abc%'; PostgreSQL 17 in an ICU en-US database scanned the table until a text_pattern_ops index existed. That class cannot serve plain < or >, so you may need both. Likewise int has no GiST class until you install btree_gist.
A plain CREATE INDEX blocks writes, though not reads, until it finishes. CONCURRENTLY lets writes continue but scans the table twice, waits for older transactions to end, and cannot run inside a transaction block. If it fails, say on a duplicate during a unique build, PostgreSQL 18 leaves an INVALID index that queries ignore but every write still maintains; drop it before retrying.
A default B-tree stores values ascending with NULLs last and can be read backward for DESC NULLS FIRST, so one index serves both default directions. Ask for DESC NULLS LAST and PostgreSQL 17 sorts instead. With several columns, a mixed order such as ORDER BY a, b DESC needs an index declared (a, b DESC); combined with LIMIT, that index returns the first rows without sorting anything.
O. Transactions and concurrency
Atomicity: a transaction's changes commit together or not at all. Consistency: each committed state satisfies the declared constraints, not your unwritten rules. Isolation: concurrent work is shielded only as far as the isolation level promises, and PostgreSQL 18's default, Read Committed, still permits anomalies. Durability: once COMMIT returns, the change survives a crash, unless asynchronous commit is enabled.
Without BEGIN, PostgreSQL 18 wraps each statement in its own implicit transaction that commits on success, so a five-statement transfer can stop halfway. BEGIN opens a block that COMMIT publishes as a unit and ROLLBACK discards. After any error inside the block, PostgreSQL refuses further statements until you end it, and a COMMIT at that point reports ROLLBACK. Some client libraries issue BEGIN for you.
Read Committed, PostgreSQL 18's default, takes a fresh snapshot per statement, so two SELECTs in one transaction can disagree. Repeatable Read keeps one snapshot for the transaction and raises an error rather than overwrite a row another transaction changed. Serializable also watches for dependency patterns that could form a cycle. Both stronger levels need retries: catch SQLSTATE 40001 and rerun the whole transaction.
Reading a change that another transaction has not yet committed is a dirty read, and if that transaction rolls back, you acted on data that never officially existed. PostgreSQL 18 accepts READ UNCOMMITTED but runs it as Read Committed, because its snapshots contain only committed rows. The standard merely permits dirty reads at that level rather than requiring them, so PostgreSQL's weakest level still never shows uncommitted data.
Under PostgreSQL 18's default Read Committed, each statement sees what was committed before that statement began, so reading the same row twice in one transaction returns two values if someone commits in between. Code that checks a balance and acts on it a moment later can act on a stale reading. Repeatable Read pins one snapshot for the transaction, and SELECT ... FOR UPDATE locks the row instead.
A phantom read is a re-run predicate query returning a different set of rows because another transaction committed a matching insert or delete in between. Locking the rows you already read cannot prevent it, since the newcomers did not exist yet. The standard permits phantoms at Repeatable Read, but PostgreSQL 18's snapshot-based Repeatable Read does not show them; Read Committed still does.
Two sessions read a balance of 100, each adds 10 in application code and writes back 110, so one deposit vanishes; PostgreSQL 17 under Read Committed does exactly that. UPDATE ... SET bal = bal + 10 ends at 120 instead, because the second update waits for the row and recomputes from the new value. Other fixes are SELECT ... FOR UPDATE, a version check, or Repeatable Read, which aborts the second writer.
Two on-call doctors each see two people on duty, and each takes only themselves off. Neither write touches the other's row, so Repeatable Read, which catches only writes to the same row, commits both and leaves nobody on call; PostgreSQL 17 does exactly that. Serializable cancels one of them. Under weaker levels you must make the conflict visible yourself, for example by locking the rows you read with FOR UPDATE.
Under Serializable, PostgreSQL 18 records reads as SIRead locks, which never block. A rw-conflict means a transaction read data a concurrent one wrote, so the reader must appear to have run first. Every snapshot anomaly contains two adjacent rw-conflicts through one pivot transaction, so on spotting that shape PostgreSQL rolls one back with SQLSTATE 40001. Not every such shape is a real cycle, so some aborts are false positives.
SELECT ... FOR UPDATE locks the rows it returns until the transaction ends, so another FOR UPDATE, UPDATE or DELETE on them waits, while plain SELECTs keep reading. In a join it locks rows from every table unless you write FOR UPDATE OF t, and it is not allowed with aggregates. It locks only rows that exist, so a concurrent INSERT of a new matching row goes through untouched.
Instead of holding a lock while a user edits, read the row with its version, then write with UPDATE ... SET ..., version = version + 1 WHERE id = $1 AND version = $2. If someone saved first, the WHERE matches nothing and the update reports zero rows, which your code must treat as a conflict. In PostgreSQL 17 under Read Committed, a writer racing on the same row waits, re-checks the WHERE, and updates zero rows.
A deadlock forms when transaction A holds a lock that B needs while B holds one that A needs, as when two transfers update the same two accounts in opposite order. Neither can proceed, so after deadlock_timeout (1 second by default) PostgreSQL 17 found the cycle and aborted one with 'deadlock detected'. Prevent it by locking in a consistent order, such as lower account id first, and retry the victim.
A PostgreSQL UPDATE writes a new row version and leaves the old one for any transaction whose snapshot still needs it, so readers see a consistent picture without waiting for writers, and writers never wait for readers. Writers do still queue behind other writers on the same row. The price is dead versions that VACUUM must remove later, and a reader can be looking at data that has already changed.
VACUUM can remove a dead row version only once no open transaction might still see it, so one forgotten session left idle in a transaction holds back cleanup for every table in the database. In PostgreSQL 17, while a Repeatable Read transaction stayed open, VACUUM reported 5000 deleted rows as dead but not yet removable. Watch pg_stat_activity for an old backend_xmin and set idle_in_transaction_session_timeout.
ROLLBACK TO SAVEPOINT s undoes only the work done after the savepoint, clears PostgreSQL's aborted-transaction state after an error, and discards any savepoints set later, while earlier work stays pending until COMMIT. That is how you try a risky statement without losing the whole block. SQLite needs it less here: a failed statement there undoes only itself and the transaction carries on.
PostgreSQL 18 runs most DDL transactionally: a CREATE TABLE or ALTER TABLE inside BEGIN vanishes on ROLLBACK, so a failed migration leaves no half-built schema. A few commands refuse to run in a block at all, including CREATE DATABASE, VACUUM and CREATE INDEX CONCURRENTLY. SQLite also rolls DDL back. MySQL 8.4 does not: CREATE, ALTER and DROP TABLE implicitly commit any open transaction first.
P. Query planning and performance
Plain EXPLAIN prints the planner's estimated costs and row counts without running anything. EXPLAIN ANALYZE executes the query and adds the actual rows, loops and time for each node, so it takes as long as the query, adds timing overhead, and performs writes for real: wrap an UPDATE or DELETE in BEGIN and ROLLBACK. The estimate explains why a plan was chosen; the actuals show whether it was right.
Read a plan from the most indented nodes outward: each node feeds its parent. In cost=0.29..14.28, the first number is the cost before the first row and the second the cost of all rows, in planner units rather than milliseconds, and a node's cost includes its children. Then compare estimated rows with actual rows at each node; the lowest node with a large gap is usually where the plan went wrong.
An index scan reads index entries, then jumps into the table for each matching row; a sequential scan reads every page once, in physical order. Between them sits the bitmap heap scan, which gathers matching row locations from an index, sorts them into physical order, then reads only those pages. A Seq Scan on a small table, or on a filter that keeps most rows, is the right plan rather than a missing index.
A nested loop runs its inner side once per outer row, so it suits a small outer input with an index on the inner join key, and it is the only strategy for a non-equality condition such as t.a > c.id. A hash join loads one input into a hash table and probes it with the other, which needs an equality condition. A merge join walks two inputs sorted on the join key, which pays off when indexes already supply that order.
Row estimates come from samples ANALYZE stores per column: the most common values with their frequencies, a histogram of the rest, the distinct count and the null fraction. Autovacuum re-analyzes after enough rows change, but a bulk load or a new expression index can leave the planner guessing until you run ANALYZE. default_statistics_target, 100 by default in PostgreSQL 18, sets how long those lists get.
Code loads 50 orders, then fetches each order's customer inside a loop: 51 queries, each paying a network round trip, a parse and an execution. Every one looks fast in the log, which is why the pattern hides; the cost is the count, and it grows with every row displayed. Fetch the related rows in a single statement, with a JOIN or with WHERE customer_id = ANY($1) passing all the ids as one array parameter.
SELECT * ships every column, including wide text and jsonb values that must be fetched and decompressed, even when the caller reads two. It also forfeits index-only scans: in PostgreSQL 17 a query on a, b was answered from an index alone, and adding one more column sent it back to the table. In a view, * is expanded once at creation, so columns added to the table later never appear.
When a predicate wraps the indexed column, as in created::date = '2026-03-01' or id::text = '5', a plain index on that column cannot be searched, and the plan shows a Seq Scan whose Filter line repeats your expression. In PostgreSQL 17, rewriting the first as created >= '2026-03-01' AND created < '2026-03-02' turned it into an Index Scan. The other fix is an index on the exact expression.
With LIMIT, the planner prices only the rows it expects to fetch, so it favors a plan that streams rows in order, such as walking an index on the ORDER BY column, and stops early. Remove the LIMIT and every row must be produced, so a scan and sort wins and the query slows. The bet can also lose: in PostgreSQL 17, LIMIT 5 on a rare flag walked 199,900 index entries and ran slower than the same query without it.
A materialized view stores a query's result as a real table, so reading it costs a scan instead of rerunning the joins and aggregates, and it can carry its own indexes. The data stays frozen until REFRESH MATERIALIZED VIEW reruns the whole query; nothing refreshes it automatically. A plain refresh can block readers, while CONCURRENTLY keeps them reading but needs a unique index on plain columns covering every row.
BUFFERS counts pages per node: shared hit means the page was already in PostgreSQL's cache, and read means it had to be fetched from outside it. PostgreSQL 18 adds BUFFERS automatically to EXPLAIN ANALYZE. A query that is fast on its second run may only have found every page cached that time, so compare read counts, not just milliseconds; with track_io_timing on, time spent reading is shown too.
When a node runs more than once, as the inner side of a nested loop does, its actual rows and time are averages per execution, and loops counts the executions. In PostgreSQL 17, actual rows=1 loops=299 meant 299 rows in total. Parallel workers count as loops too: rows=2667 loops=3 totaled 8,000. PostgreSQL 17 shows that average as a whole number, while 18 prints two decimals.
Each node's row estimate feeds the cost of every node above it, so an error near the leaves propagates upward. In PostgreSQL 17, two correlated filters on 100,000 rows were estimated at 1 row against 100 actual, and the join above inherited the error, estimating 2 rows per worker against 150. An underestimate like that is what makes a nested loop look cheap; repair the lowest diverging node, not the join.
The planner multiplies the selectivities of separate conditions as if columns were independent, so city = 'c5' AND zip = 'z5', where zip follows from city, is underestimated. In PostgreSQL 17 that estimate was 1 row against 100 actual; after CREATE STATISTICS ... (dependencies) ON city, zip FROM t and ANALYZE it was 100. Dependencies help only equality and IN against constants; ndistinct and mcv objects cover more.
A parallel plan splits a scan among worker processes whose rows meet at a Gather or Gather Merge node. PostgreSQL 18 quietly plans serially when the query writes or locks rows, runs from a cursor, or calls any function marked PARALLEL UNSAFE, which is the default for every user-defined function. Workers can also run out at execution time, so compare Workers Planned with Workers Launched.
A prepared statement first plans each execution with the actual parameter values. After five such custom plans, PostgreSQL 18 compares a generic plan built without the values and switches to it if its estimated cost is not much worse; EXPLAIN then shows $1 where the value was. On skewed data, a plan right for common values can be wrong for a rare one. plan_cache_mode = force_custom_plan keeps replanning.
Q. Security
Injection happens when input is pasted into SQL text before the server parses it, so the input can close a string literal and carry on as syntax: ' OR '1'='1 turns a lookup into a condition that is always true. The parser cannot tell your code from the attacker's, because both arrive as one string. Over PostgreSQL's simple query protocol, a ; can even append a second statement. The cure is keeping values out of the text.
With a bound parameter, the SQL text containing $1 is parsed on its own and the value travels separately, so it is only ever data: nothing in it is read as syntax, and no escaping happens or is needed. Escaping instead edits the text and depends on getting every quoting rule right. Placeholders stand only for values, never for table or column names, and libpq's PQexecParams also accepts just one statement per call.
Run the application as a role that holds only what it needs, such as SELECT, INSERT and UPDATE on its own tables, while a separate role owns the schema and runs migrations. An owner can do anything to its objects, including DROP, so an application connected as owner turns any injection into a schema-level breach. REVOKE takes a grant back; in PostgreSQL 18 new functions are executable by PUBLIC until you revoke that.
After ALTER TABLE ... ENABLE ROW LEVEL SECURITY, PostgreSQL 18 adds each applicable policy's condition to every query on the table, so a tenant filter holds even when application code forgets its WHERE. With RLS on and no policy, a default deny hides every row. Superusers, BYPASSRLS roles and, by default, the owner skip policies, and unique and foreign-key checks ignore them, which can reveal that a hidden row exists.
Granting SELECT on a view but not its table limits a role to the columns and rows the view exposes, because by default the view reads the table with its owner's privileges. Row-level security is applied as that owner too, so a view owned by the table's owner skips the policies: in PostgreSQL 17 it returned two rows where security_invoker = true returned one. A leaky function in WHERE can see filtered rows unless the view is security_barrier.
A SECURITY DEFINER function runs with its owner's privileges, so callers can do through it whatever its body does as that owner, and inside it current_user reports the owner rather than the caller. That makes it the tool for exposing one narrow operation on a table nobody else may touch. PostgreSQL grants EXECUTE on new functions to PUBLIC, so revoke that in the transaction that creates the function.
Values can travel as parameters, but identifiers cannot, so a dynamic table or column name has to be spliced into the text. Splice it with format('%I', name) or quote_ident, which double-quotes the name and escapes embedded quotes, so drop table x;-- becomes one harmless identifier. Use %L for a literal you cannot pass with EXECUTE ... USING, and check names against a list of known tables when you can.
log_statement records the text of statements, not their effects: no affected rows, no old values. Under mod, PostgreSQL 17 logged an UPDATE but not SELECT bump(), although that function updated rows, and even under all the statements inside the function body never appeared. Statements with syntax errors are not logged either. The log can itself expose bind parameter values and plaintext passwords.
PostgreSQL 18 has only roles: one with LOGIN acts as a user, and any role granted to another acts as its group. A member granted with INHERIT uses the group's privileges automatically; one granted with SET can SET ROLE to become the group, after which new objects belong to the group. Attributes such as CREATEDB and SUPERUSER are never inherited, even through INHERIT; you must SET ROLE to a role that holds them.
GRANT covers only objects that exist now, so a table created next week stays private to its creator. ALTER DEFAULT PRIVILEGES stores grants applied at creation time, but only for objects created by the role it names, or by you when none is named; group membership does not carry them over. If migrations run as deployer, write ALTER DEFAULT PRIVILEGES FOR ROLE deployer GRANT SELECT ON TABLES TO reader.
Every role belongs to PUBLIC, and in PostgreSQL 18 PUBLIC already holds CONNECT and TEMPORARY on each new database, EXECUTE on new functions, and USAGE on languages and types. Tables, sequences and schemas start with no PUBLIC grants, except that PUBLIC has USAGE on the public schema. PostgreSQL 15 stopped granting PUBLIC CREATE there, but a database upgraded from 14 or earlier keeps it until you revoke it.
USING filters existing rows: rows it rejects are invisible to SELECT and silently skipped by UPDATE and DELETE, which report zero rows, not an error. WITH CHECK judges the new row an INSERT or UPDATE would write, and a failure raises an error. When an ALL or UPDATE policy omits WITH CHECK, its USING doubles as the check, so in PostgreSQL 17 a user could neither see another tenant's row nor hand their own row over to it.
Permissive policies, the default, are combined with OR, so adding a broad FOR SELECT USING (true) beside a per-tenant policy let a user in PostgreSQL 17 read every row. Restrictive policies are combined with AND on top, so each must also pass; use them for rules that always apply, such as a region check. At least one permissive policy must grant access first, because restrictive policies alone leave every row hidden.
Superusers and roles with BYPASSRLS always skip row-level policies, and so does the table's owner until you run ALTER TABLE ... FORCE ROW LEVEL SECURITY. In PostgreSQL 17 the owner saw both rows of an RLS-enabled table with no policy, and none once FORCE was set. An application that connects as the owner therefore never meets its own tenant policies, and tests run as the owner pass while isolation is broken.
Unqualified names in a SECURITY DEFINER function resolve through the session's search_path, and the temporary schema, writable by anyone, is searched first for tables. In PostgreSQL 17 a caller created a temp table with the same name as the one the function checked, and the function trusted the impostor. Add SET search_path = trusted_schema, pg_temp to the function so pg_temp comes last, or schema-qualify every name.
A parameterized INSERT stores a value like x'; DROP TABLE t; -- safely, exactly as typed. The attack fires later, when other code, often a report, a maintenance job or a function building dynamic SQL, reads that value back and concatenates it into a new statement, trusting it because it came from the database. Treat values read from your own tables as untrusted too: bind them, or quote them with %L and %I.
R. Schema design and normalization
First normal form asks each column to hold one value, not a list. A tags column holding 'red,blue' cannot be indexed per tag or carry a foreign key, and turns 'rows tagged blue' into LIKE '%blue%', which also matches 'lightblue'. Move the list into a child table with one row per value. A PostgreSQL array is a typed alternative, but its elements still cannot reference another table.
Second normal form forbids a non-key column that depends on only part of a composite key: in order_lines(order_id, product_id, product_name), the name depends on product_id alone, so it repeats on every line and can drift. Third forbids a non-key column that depends on another non-key column, as when zip determines city. The repair is the same for both: move the column to the table keyed by what determines it.
Storing a copied or derived value, such as an order total or the customer's name on each order, saves joins and aggregation when reading. Every write must then keep the copy correct, through application code, a trigger or a scheduled refresh, and any path that skips it leaves the copy silently wrong. Try an index or a materialized view first: each keeps the copy in one place the database maintains or rebuilds for you.
Each child row carries its parent's key, so orders.customer_id references customers.id, and the parent holds no list of children. Putting the key on the one side would need a list in a single column, which breaks first normal form. Make the column NOT NULL when every child must have a parent, and index it yourself, because PostgreSQL 18 does not index the referencing side of a foreign key automatically.
Students and courses need a third table holding one row per pairing, with a foreign key to each side. PRIMARY KEY (student_id, course_id) stops the same pair from being recorded twice and gives you an index led by student_id; queries that start from a course need a second index on course_id. Facts about the pairing itself, such as the enrollment date or grade, belong in that join table.
One NULL covers several meanings: the value exists but is unknown, it does not apply, or it was never collected. Queries cannot tell these apart, and three-valued logic treats them all as unknown, so WHERE end_date > now() silently drops every open-ended row. When the difference matters, record it explicitly with a status column or a separate table, and declare NOT NULL wherever a value is always required.
A soft delete sets deleted_at instead of removing the row, so it can be restored or audited. The cost lands on every later query: each must remember WHERE deleted_at IS NULL, a UNIQUE email now blocks re-creating a deleted account unless you switch to a partial unique index on live rows, and foreign keys still treat deleted parents as present. A view over the live rows narrows the missed-filter risk.
created_at with DEFAULT now() fills itself, but only when the insert omits the column, so an explicit value overrides it. updated_at has no automatic form in PostgreSQL 18: a default never fires on UPDATE, so it needs a BEFORE UPDATE trigger or disciplined application code. Who made the change is harder, because a pooled application usually connects as one database role; pass the acting user explicitly.
Entity-attribute-value stores each field as a row of (entity_id, attribute, value), usually with a text value. Types vanish, so numbers compare as strings and '10' sorts before '9', and NOT NULL, CHECK and foreign keys can no longer target one attribute. Rebuilding a record takes a join or pivot per attribute, and the planner keeps no statistics per attribute. A jsonb column is the usual alternative when fields vary.
Range partitioning splits a table by intervals of a key, such as months, each including its lower bound and excluding its upper; list partitioning assigns explicit values, such as regions. Only a test on the key itself prunes: in PostgreSQL 17 a date filter read one partition, but a test on any other column, or on extract(month FROM at), scanned them all. A row fitting no partition fails unless a DEFAULT one exists.
S. JSON and semi-structured data
json keeps your exact input text and reparses it on every read; jsonb parses once into a binary form, so writes cost a little more and reads far less. Only jsonb has containment and existence operators, GIN indexing and an equality operator. json has none, so SELECT DISTINCT or GROUP BY on a json column fails. Keep json only when the original text must come back byte for byte.
On a jsonb value in PostgreSQL 17, -> returns jsonb, ready to chain or compare as JSON, while ->> returns text, the form you print or cast. #> and #>> take a path array such as '{a,b}' and split the same way. Comparing ->> output directly sorts as text, so '5' > '10' is true; cast first, as in (doc->>'n')::int > 10. A missing key or index gives SQL NULL from all four, not an error.
A jsonpath is a small language of its own: $ is the whole document, .key and [*] walk into it, and ? (...) filters with @ as the current item. Containment with @> only asks whether a fragment appears, while a path can test ranges and walk nested arrays. In the default lax mode a path that meets the wrong shape returns nothing; prefix it with strict to make that an error. Array subscripts in a path start at 0.
A GIN index on a jsonb column serves operators applied to the column itself, such as @>, ? and @?. It does nothing for doc->>'email' = $1, which compares one extracted value and falls back to a sequential scan; build an expression B-tree on ((doc->>'email')) for that lookup. The jsonb_path_ops class makes a smaller GIN index that still serves @>, @? and @@ but drops the key-existence operators.
A key you filter, join or constrain on in every row is a column in disguise. Inside JSON it can never carry a foreign key; NOT NULL or a type rule needs a hand-written CHECK on an extracted path; and every query must extract and cast it again. Keep JSON for attributes that genuinely vary by row, such as a vendor payload stored as received. Changing one key still locks and rewrites the whole row.
a @> b is true when every key, value and array element of b appears in a at the same depth; <@ asks the reverse. Array order and repeats are ignored, but nesting is not, so {"a":{"b":1}} does not contain {"b":1}. A GIN index on the column, default or jsonb_path_ops, serves @> but not <@, which makes @> the indexable way to say that a key holds a value.
? tests one key, ?| any key from a text array and ?& all of them. They read only the top level: a key inside a nested object is invisible, and on an array they match string elements only, so '[1,2]'::jsonb ? '1' is false. To look deeper, apply the operator to an extracted value such as doc->'tags' ? 'x', which then needs its own expression index. Only the default GIN class indexes these operators.
jsonb_array_elements yields one row per element and jsonb_each one row per top-level key, and their _text variants return text. Placed in FROM after a table, the call is lateral, so a source row repeats once per element, and a row whose array is empty or NULL disappears. Write LEFT JOIN LATERAL jsonb_array_elements(...) ON true to keep it. Handing an object to the array function raises an error.
jsonb_build_object takes alternating keys and values and converts each SQL value to JSON, so an SQL NULL becomes a JSON null. jsonb_agg folds rows into an array and accepts its own ORDER BY, but over zero rows it returns SQL NULL rather than [], so wrap it as COALESCE(jsonb_agg(...), '[]'). row_to_json turns a whole row into an object keyed by its column names.
jsonb_set(doc, '{a,b}', val) replaces the value at a path and can add the final key, but if an earlier key is missing it returns the document unchanged, with no error. It is strict, so a NULL val makes the result NULL and an UPDATE wipes the column; jsonb_set_lax handles that case. || merges top-level keys only, replacing a nested object whole, and - or #- removes a key or a path.
Converting to jsonb keeps only the last copy of a repeated key, drops insignificant whitespace and stores keys in its own order, shorter keys first. Numbers pass through numeric, so 1.230e-5 comes back as 0.00001230 and 1.0 equals 1. Two documents that differ only in these ways compare equal. When the original text matters, for a signature or an audit trail, store json or text instead.
'null'::jsonb is a real value: IS NULL is false for it and jsonb_typeof reports null. A missing key is a third state, yet ->> returns SQL NULL for both, so doc->>'a' IS NULL cannot tell them apart. Test presence with doc ? 'a', or compare doc->'a' = 'null'::jsonb. jsonb_build_object turns an SQL NULL into JSON null, so the key then exists; jsonb_strip_nulls removes such keys.
@? asks whether a path returns any item, so it pairs with a filter: doc @? '$.n[*] ? (@ > 8)'. @@ evaluates a path that is itself a predicate, such as doc @@ '$.n[*] > 8', and returns NULL when the result is not a boolean. Inside a filter @ is the current item, and jsonb_path_exists(doc, path, vars) binds $name variables. GIN indexes serve both, narrowing on == comparisons with constants.
T. Views, functions and procedural extensions
A view stores only its query, so every use reruns it against current data: never stale, and never faster than the query itself. SELECT * is expanded when the view is created, so a column added to the table later does not appear until you redefine the view. Names and types come from the query, and an unlabeled literal becomes a text column called ?column?, so name and cast such columns yourself.
In PostgreSQL 17 a view is automatically updatable when it reads exactly one table or updatable view and has no top-level WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, set operation, aggregate or window function. Even then a computed column stays read-only, and writing to it raises an error. For anything more complex, an INSTEAD OF trigger receives each row written to the view and performs the changes on the base tables itself.
REFRESH MATERIALIZED VIEW reruns the query and replaces the stored rows under an ACCESS EXCLUSIVE lock, so readers wait for the whole refresh. CONCURRENTLY keeps reads flowing by diffing old rows against new ones, which needs a unique index on plain columns with no WHERE clause and a view that is already populated. It tends to be slower when most rows change, and the data is only as fresh as the last refresh.
Volatility is a promise to the planner. VOLATILE, the default, is re-run for every row and cannot drive an index scan. STABLE gives one result per statement, so WHERE id = f() can use an index. IMMUTABLE may be folded to a constant at plan time and is required inside an index expression. Mark anything that reads TimeZone or a table STABLE, not IMMUTABLE, or a cached plan can keep a stale value.
A row-level trigger runs once per affected row; a statement-level trigger runs once per statement, even one that touches zero rows. A row-level BEFORE trigger can rewrite NEW or skip the row, while a row-level AFTER trigger fires at the end of the statement, sees the final row, and suits writes to other tables. Several triggers on one event fire in alphabetical order by name, not in creation order.
Trigger logic never appears in the statement you are reading, so an INSERT that writes three tables, or fails, can look inexplicable. The error's CONTEXT line names the trigger function, \d table lists the triggers, and EXPLAIN ANALYZE reports how many times each one ran. Triggers also fire for foreign-key cascade actions, and a trigger that writes its own table can fire itself again, with no built-in depth limit.
PostgreSQL records which columns each view reads, so DROP TABLE refuses while a view depends on the table, and ALTER COLUMN ... TYPE refuses on any column a view uses. DROP ... CASCADE removes the dependent views too, with their grants, and reports them only as NOTICE lines. The usual migration drops the views, alters the column and recreates the views in one transaction, from definitions kept in version control.
CREATE OR REPLACE VIEW compares the new query position by position: same names, same types, same order, with new columns allowed only at the end. Renaming needs ALTER VIEW ... RENAME COLUMN, and retyping means DROP and CREATE, which dependent views block. Because only names and types are checked, swapping two same-typed expressions under the old names passes silently and changes what readers get.
An automatically updatable view filters what you can see, not what you can write. Without a check option an INSERT through it can add a row the view will never show, and an UPDATE can move a row out of it. WITH CHECK OPTION rejects such a row. CASCADED, the default, also tests the conditions of the views underneath; LOCAL tests only this view's own WHERE clause.
PostgreSQL expands a view into the calling query before planning, so your filters reach the base tables and output columns you never select are not computed. The same expansion means a view on a view on a view is planned as one large query, with joins you cannot see in the SQL you wrote. Run EXPLAIN on the outer query to see the real cost; a plain view caches nothing.
A materialized view is stored like a table, so you can add B-tree, GIN or unique indexes to it, and a refresh keeps them current. A plain view holds no rows, so CREATE INDEX on one fails; index its base tables instead. A unique index on plain columns is what REFRESH ... CONCURRENTLY requires, so add one to any matview you plan to refresh while people are reading it.
U. Dialect differences and portability
OFFSET n ROWS FETCH FIRST m ROWS ONLY is the SQL-standard spelling and LIMIT m OFFSET n the common one. PostgreSQL 17 accepts both and adds WITH TIES to the standard form. SQLite 3.51 and MySQL 8.4 accept only LIMIT, and SQL Server's OFFSET and FETCH belong to its ORDER BY clause, so they cannot appear without one. In every engine, paging without ORDER BY returns rows in no promised order.
|| concatenates in PostgreSQL, SQLite and the standard, and yields NULL if either side is NULL. SQL Server long offered only + and added || in SQL Server 2025, while in MySQL 8.4 || means logical OR unless PIPES_AS_CONCAT is set. CONCAT() exists in all four but disagrees on NULL: PostgreSQL, SQLite 3.51 and SQL Server skip NULL arguments, while MySQL's returns NULL if any argument is NULL.
The standard quotes identifiers with double quotes, as PostgreSQL and SQLite do. MySQL 8.4 uses backticks unless ANSI_QUOTES is set, and SQL Server accepts brackets or double quotes. In PostgreSQL a quoted name keeps its case, so "Order" and order are different objects. SQLite 3.51 treats a double-quoted name that matches no column as a string literal, so a typo returns text instead of an error.
PostgreSQL's serial is shorthand for a sequence plus a nextval default, and the SQL-standard GENERATED ... AS IDENTITY declares the same thing as a column property. MySQL 8.4 writes AUTO_INCREMENT and SQL Server IDENTITY(1,1). In SQLite 3.51 an INTEGER PRIMARY KEY already aliases the rowid; adding AUTOINCREMENT only stops reuse of the ids of deleted rows, at extra cost.
PostgreSQL 17 writes INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col, naming the unique constraint it arbitrates on, and SQLite 3.51 accepts the same form. MySQL 8.4's ON DUPLICATE KEY UPDATE reacts to a collision on any unique index, and its manual advises against it on tables that have several. In PostgreSQL one statement may not update the same row twice, so a batch holding a key twice fails.
SQLite gives a column a type affinity, not a type: an INTEGER column converts '12' to the integer 12 but keeps 'x12' as text, with no error. typeof(col) shows what each row really holds, and comparisons follow those storage classes rather than the declared type. STRICT tables, added in 3.37.0, reject a value of the wrong type, which is what PostgreSQL does for every table.
SQLite has no boolean storage class: TRUE and FALSE are aliases for 1 and 0, and typeof(true) is integer. MySQL 8.4's BOOLEAN is a synonym for TINYINT(1), so the column will store 7. SQL Server uses bit, which turns any nonzero value into 1. PostgreSQL's boolean is a distinct type, so true + 1 is an error there rather than 2, and porting code has to make each conversion explicit.
Adding a month is + interval '1 month' in PostgreSQL, DATE_ADD(d, INTERVAL 1 MONTH) in MySQL 8.4, DATEADD(month, 1, d) in SQL Server and date(d, '+1 month') in SQLite. Results differ too: from March 31, PostgreSQL 17 lands on April 30 and SQLite 3.51 on May 1. MySQL's DATEDIFF(a, b) returns a minus b, while SQL Server's DATEDIFF(day, a, b) returns b minus a.
MySQL 8.4's default collation, utf8mb4_0900_ai_ci, makes 'abc' = 'ABC' true and ignores accents as well. PostgreSQL's default collations are deterministic, so the same test is false there; use ILIKE, lower(col) = lower($1) backed by an expression index, or a case-insensitive type or collation. SQLite 3.51 compares with = case-sensitively, yet its LIKE ignores case for ASCII letters only.
Standard SQL rejects a selected column that is neither grouped nor aggregated unless it is functionally dependent on the grouped columns; PostgreSQL recognizes that dependency only through a grouped primary key. Older MySQL defaults accepted the query and returned a value from an arbitrary row of each group. MySQL 8.4 enables ONLY_FULL_GROUP_BY by default, but turning it off brings that back, and SQLite 3.51 still accepts such queries.
PostgreSQL reads back written rows with RETURNING, SQLite added it in 3.35.0, and SQL Server uses an OUTPUT clause. MySQL 8.4 has none of these, so you call LAST_INSERT_ID(), which reports only the first id of a multi-row insert. A last-insert id is tracked per connection and only changes when a row is inserted, so after a SQLite upsert that updated, last_insert_rowid() still names an older row.
PostgreSQL 8.4 shipped WITH queries, recursive ones included, and window functions in 2009. MySQL gained both only in 8.0, and SQLite added CTEs in 3.8.3 (2014) and window functions in 3.25.0 (2018). Oracle, by contrast, offered analytic window functions years before PostgreSQL did. Before relying on either feature, check the oldest server version your code must still run on.
information_schema is the SQL-standard set of catalog views, present in PostgreSQL, MySQL and SQL Server, so a portable script can list tables and columns from it. It leaves out engine-specific objects: PostgreSQL indexes, for example, appear only in pg_catalog views such as pg_indexes. SQLite 3.51 has no information_schema at all; read sqlite_schema and PRAGMA table_info instead.
MySQL 8.4 compares a string with a number as floating point, so WHERE varchar_col = 0 matches every string with no leading number, such as 'grape', with only warnings even in strict mode, and cannot use its index. PostgreSQL 17 raises operator does not exist: character varying = integer, and an unconvertible literal such as 5 = '5x' fails outright. SQLite 3.51 converts by column affinity and runs either comparison without error.
V. Full-text search
to_tsvector('english', text) reduces a document to normalized lexemes with positions and drops stop words, so 'The cats were running' becomes 'cat':2 'run':4. A tsquery combines lexemes with AND, OR, NOT and the <-> adjacency operator, and @@ tests one against the other. Matching works on lexemes, not substrings: a search for 'run' finds 'running', but a misspelled word finds nothing.
A configuration such as english or simple chooses the stemmer and stop-word list, and the document and the query must use the same one: an english vector stores cat, so a simple query for cats misses it. The one-argument to_tsvector(text) reads default_text_search_config, which can differ per session, so only the two-argument form is allowed in an index expression.
to_tsquery expects operator syntax and raises a syntax error on raw input such as fat cat (. plainto_tsquery ANDs the words together and phraseto_tsquery requires them adjacent. websearch_to_tsquery understands quoted phrases, or and a leading -, and never raises, which makes it the safe choice for a search box. A query made only of stop words comes out empty and matches nothing.
@@ only says whether a document matches; ts_rank scores each match by term frequency and ts_rank_cd by how close the terms sit. Ranking reads every matching row's vector, so a broad query ranks many rows before LIMIT can cut them, and no core index helps with that ordering. Without a normalization flag, length is ignored, and setweight lets a title count more than body text.
GIN is the preferred full-text index, an inverted list of lexemes that keeps neither weights nor positions, so weighted and phrase (<->) queries recheck table rows. GiST keeps a lossy fixed-length signature per row, so every hit is rechecked. Index a stored generated column such as to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')); without coalesce, one NULL field makes the whole vector NULL.
Stemming cannot rescue a misspelling: 'postgersql' shares no lexeme with 'postgresql'. The pg_trgm extension compares sets of three-character chunks instead, scoring them with similarity(), and % matches above pg_trgm.similarity_threshold, 0.3 by default. A GIN or GiST index built with gin_trgm_ops or gist_trgm_ops also speeds LIKE '%term%' and ILIKE, which a B-tree cannot serve.
W. Arrays
ARRAY[10,20,30] and '{10,20,30}'::int[] build the same value. Subscripts start at 1, so [0] returns NULL instead of an error, as does any index past the end, and a slice like [2:3] returns a smaller array. In a braced literal an unquoted NULL is a null element, while "NULL" is a four-letter string. array_length of an empty array is NULL, not 0; cardinality returns 0.
x = ANY(arr) tests membership like IN, but the whole list travels as one array value, so a prepared statement WHERE id = ANY($1) takes any number of ids without changing its text. An empty array is legal and matches nothing, whereas IN () is a syntax error. x <> ALL(arr) behaves like NOT IN, trap included: one NULL element makes the result NULL rather than true.
@> asks whether the left array holds every element of the right, <@ the reverse, and && whether the two share any element. Order and duplicates are ignored, so ARRAY[1,2,3] @> ARRAY[3,3] is true. A GIN index on the column serves all three operators but not 5 = ANY(col); rewrite that filter as col @> ARRAY[5] and the index gets used.
unnest(arr) returns one row per element, and WITH ORDINALITY adds a position column counting from 1, the reliable way to keep the original order once the rows are joined or grouped. Given several arrays in FROM, unnest(a, b) lines them up side by side and pads the shorter one with NULLs. array_agg(x ORDER BY pos) makes the return trip.
An array is a single value: no foreign key can point from its elements, one element cannot carry columns of its own, and changing one element rewrites the whole array. The PostgreSQL manual warns that searching for particular elements can be a sign of misdesign. Keep arrays for small lists read whole, such as tags, and move to a child table when elements need constraints, joins or their own data.
X. Dates, times and time zones
Applied to a timestamptz, AT TIME ZONE 'America/New_York' returns that zone's wall-clock time as a plain timestamp; applied to a plain timestamp, it reads the wall time as local and returns the instant as timestamptz. The input type sets the direction. A text offset follows POSIX signs, so 'UTC+5' and '+05:00' both mean five hours west; write INTERVAL '+05:00' to mean east.
A timestamptz stores one UTC instant and keeps no zone; each session displays it in its own TimeZone, so the same value prints as 08:00-04 in New York and 21:00+09 in Tokyo. Text without an offset is also read in the session's zone. A plain timestamp ignores the setting entirely, so a value written from one zone is silently misread from another.
On a spring-forward day, 02:30 in New York never happened, yet PostgreSQL accepts it, applying the offset in force just before the jump, so it shows as 03:30 EDT. On a fall-back day 01:30 happened twice, and it takes the offset in force just after, the standard-time reading. Give an explicit offset for the other instant, and expect + interval '1 day' and + interval '24 hours' to differ on those dates.
date - date returns an integer count of days, ready for arithmetic, while timestamp - timestamp returns an interval such as 9 days 06:00:00. Between two New York midnights, timestamptz subtraction reports 23:00:00 across the March change and 1 day 01:00:00 across the November one, because it measures elapsed time. Use extract(epoch from ...) for seconds and age() for a calendar difference.
BETWEEN '2024-03-01' AND '2024-03-31' on a timestamp column stops at the midnight that begins March 31, dropping nearly the whole last day, and patching it with 23:59:59 still misses fractional seconds. Write ts >= '2024-03-01' AND ts < '2024-04-01' instead: consecutive ranges then neither overlap nor leave gaps, and the bare column can still use a B-tree index.
date_trunc('day', ts) on a timestamptz cuts at midnight in the session's TimeZone, so the same query buckets rows differently per connection, and truncating in UTC splits a New York day at 7 or 8 p.m. local time. Name the zone with the three-argument form, date_trunc('day', ts, 'America/New_York'), or group on (ts AT TIME ZONE zone)::date.
Pattern letters are easy to swap: MM is the month, MI the minute, and HH a 12-hour clock, so 'HH:MM' renders 15:42 on a July day as 03:07. Plain Month always prints English names padded to nine characters, while the TM prefix follows lc_time, so output varies with the server's locale; FM strips the padding. to_timestamp returns a timestamptz read in the session zone.
Y. Sequences, identity and generated keys
nextval is never undone: a rolled-back transaction keeps the number it drew, and the next caller gets the following one. That lets concurrent inserts proceed without waiting on each other, and it is why gaps are normal. An INSERT ... ON CONFLICT draws a value even when it ends up updating, and a failed insert spends one too. Treat sequence values as unique labels, never as a count.
currval returns the last value this session drew and raises an error until the session has called nextval; other sessions never change it. A bulk load with explicit ids leaves the sequence behind, so reset it with setval(pg_get_serial_sequence('t','id'), (SELECT max(id) FROM t)). Pass false as a third argument when the given value itself should be handed out next.
GENERATED ALWAYS AS IDENTITY rejects an INSERT that supplies the column unless it adds OVERRIDING SYSTEM VALUE, while BY DEFAULT takes the supplied value without complaint. Either way an explicit value does not advance the sequence, so a later default can collide with it on the primary key. ALTER TABLE t ALTER COLUMN id RESTART WITH n moves the counter past such values.
With CACHE n above the default of 1, each session reserves a block of n values at once; any it never uses are lost when it disconnects, and values from two sessions interleave out of order. Rollbacks and failed inserts leave gaps even without a cache. The guarantee is uniqueness, not density or commit order, so never infer row counts or insertion order from the ids.
A series that must have no holes, such as invoice numbers, cannot come from a sequence. Keep a counter row and run UPDATE counters SET n = n + 1 WHERE name = 'invoice' RETURNING n in the same transaction as the insert: the row lock makes concurrent writers wait their turn, and a rollback undoes the increment. The price is that numbering serializes, so keep those transactions short.
Z. Partitioning and inheritance
CREATE TABLE ... PARTITION BY makes a parent that stores no rows itself. Each partition is a real table with its own bounds, and inserts are routed to it by the partition key; a row that fits no partition fails unless a DEFAULT partition exists. Partitioning pays off for very large tables you prune or archive by key. On a small table it only adds planning work.
The planner skips partitions only when the WHERE clause constrains the partition key itself: created = '2024-02-10' reads one partition, while a filter on another column, or on an expression such as created + 1, reads them all. Choose the key your busiest queries filter on, and compare the bare column with constants or parameters rather than wrapping it in a function.
RANGE suits values that grow, such as dates, so old ranges can be dropped whole; a bound includes its FROM value and excludes its TO value. LIST suits a small fixed set of categories, like regions, with a DEFAULT partition catching the rest. HASH spreads rows evenly by modulus and remainder when no natural range exists, but a range filter cannot prune it, and nothing ages out.
DETACH PARTITION turns a partition back into an ordinary table, and dropping that table frees the space without the dead rows and VACUUM work of a bulk DELETE. ATTACH PARTITION takes only SHARE UPDATE EXCLUSIVE on the parent but scans the incoming table to validate its bounds; add a matching CHECK constraint first and the scan is skipped. DETACH ... CONCURRENTLY avoids blocking queries on the parent.
Each partition enforces uniqueness only within itself, through its own index, so PostgreSQL 17 rejects a primary key or unique constraint on a partitioned table unless it includes every partition-key column. A key of (id, created) is accepted, but it no longer stops one id appearing in two months. If id alone must be unique, only a single generator such as one sequence can promise it.
Before declarative partitioning, a child created with INHERITS added its rows to queries on the parent, and ONLY parent left them out. CHECK and NOT NULL constraints are inherited, but indexes, unique and primary keys, and foreign keys are not, so a parent's primary key cannot stop a child repeating its values. An INSERT into the parent stays in the parent; nothing routes it to a child.
AA. Locking, blocking and retry
A plain UPDATE locks each row it changes until commit, as FOR NO KEY UPDATE, or FOR UPDATE when it changes a uniquely indexed key. Readers never wait on it, but a second UPDATE, DELETE or SELECT ... FOR UPDATE of the same row waits until the first transaction ends. One transaction left open after an UPDATE therefore blocks every later writer of those rows, indefinitely unless lock_timeout is set.
Every statement takes a table lock, and the mode names are historical. ROW EXCLUSIVE, taken by INSERT, UPDATE and DELETE, conflicts with SHARE but not with itself, so writers coexist, while CREATE INDEX takes SHARE and blocks them. ACCESS SHARE, taken by plain SELECT, conflicts only with ACCESS EXCLUSIVE, which DROP, TRUNCATE, VACUUM FULL and most ALTER TABLE forms take.
An ALTER TABLE that needs ACCESS EXCLUSIVE waits behind any open transaction that has touched the table, and while it waits, every new query on that table, plain SELECTs included, queues behind it. One slow report can therefore stall the whole application. Run DDL after SET lock_timeout = '2s' and retry on failure, so the ALTER gives up instead of holding the queue.
Workers claiming jobs with plain SELECT ... FOR UPDATE all queue on the same first row. With SKIP LOCKED each worker passes over rows another transaction holds, so ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED hands out separate batches. NOWAIT raises an error at once if a row is locked. Both deliberately see an incomplete picture, which suits a queue and would mislead a report.
pg_advisory_lock(key) locks a number whose meaning your application decides; PostgreSQL enforces nothing about it. A session-level lock survives ROLLBACK, stacks if taken twice, and holds until explicitly released or the session ends, while pg_advisory_xact_lock releases at commit or rollback. Behind a transaction pooler use the transaction form, since a session lock stays on a connection someone else gets next.
PostgreSQL ends one side of a deadlock with SQLSTATE 40P01, and under REPEATABLE READ or SERIALIZABLE a conflicting transaction fails with 40001. Neither is a bug to report: catch them and rerun the whole transaction, including the reads that decided what to write, not only the failed statement. Bound the number of attempts, and take locks in a consistent order to make deadlocks rarer.
AB. MVCC, physical storage and vacuum
Under MVCC an UPDATE never overwrites: it writes a new row version, stamps the old one's xmax with its transaction id, and leaves both in the table. Each snapshot picks the version it may see, which is why readers never block writers. The price is dead versions that keep their space until VACUUM removes them, so a table updated heavily can grow while its row count stays flat.
A heap-only tuple (HOT) update happens when the UPDATE changes no indexed column, BRIN aside, and the new version fits on the same page. No index entries are written, and old versions can be pruned during ordinary reads. Leave room with a lower fillfactor on heavily updated tables and watch n_tup_hot_upd in pg_stat_user_tables; an index on a frequently changed column rules HOT out for those updates.
An index-only scan answers from the index, but it must still read the heap for any page the visibility map does not mark all-visible, and VACUUM is what sets those bits. EXPLAIN ANALYZE reports the cost as Heap Fetches: a freshly updated table can show thousands, and the same query shows zero after a VACUUM. A covering index cannot help much on a table changed faster than it is vacuumed.
Plain VACUUM removes dead row versions and records their space as free for new rows in the same table, without an exclusive lock. It returns space to the operating system only when whole pages at the end of the table are empty, so a table that once bloated usually stays that size on disk. That is normally fine: the goal is a steady size kept flat by reuse, not the smallest possible file.
VACUUM FULL writes a compact copy of the table and its indexes, so it needs room for the copy and holds ACCESS EXCLUSIVE throughout, blocking even plain SELECTs. Reserve it for when a lot of space must come back. The third-party pg_repack extension rebuilds a table while holding the exclusive lock only briefly at the start and end, but vacuuming often enough avoids needing either.
Autovacuum starts on a table once its updated or deleted rows exceed 50 plus 20% of its row count, the defaults in PostgreSQL 17. On a 100-million-row table that means 20 million dead rows before anything happens. PostgreSQL 18 adds autovacuum_vacuum_max_threshold, 100 million by default. For a hot table, lower the factor per table with ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01).
Transaction ids are 32 bits, so every table must be vacuumed at least once per two billion transactions to freeze old rows, or past rows would start to look like future ones. Autovacuum forces that freeze when a table's oldest unfrozen id passes autovacuum_freeze_max_age, 200 million by default, even with autovacuum off. Ignore the warnings and the server stops assigning new transaction ids until a VACUUM runs.
AC. Server-side routines and error handling
A function returns a value and runs inside the caller's transaction, so it can never COMMIT. A procedure, invoked with CALL, may COMMIT or ROLLBACK between steps, which suits batch jobs that commit every few thousand rows. That only works when CALL runs at top level: inside an explicit BEGIN block its COMMIT fails with invalid transaction termination, and SELECT proc() is rejected outright.
RETURNS TABLE (...) or RETURNS SETOF type makes a function produce rows. Call it in FROM, as in SELECT * FROM f(42), to get real columns; in the select list you get one composite value per row. In PL/pgSQL the RETURNS TABLE column names become variables, so a bare id in the body's query is ambiguous and raises an error; qualify table columns with an alias.
A block is an optional DECLARE section, then BEGIN, its statements, an optional EXCEPTION section, and END. This BEGIN and END only group statements; they never start or end a transaction. Blocks nest, and an inner variable hides an outer one of the same name until the inner END; label the outer block, as in <<outer>>, and write outer.qty to reach the hidden one.
Entering a block that has an EXCEPTION clause starts a subtransaction, so on an error the block's database changes roll back while variables keep their values. That bookkeeping costs even when nothing fails: a 200,000-pass loop ran about four times slower on PostgreSQL 17 with an empty handler inside. Test for the condition first, or use INSERT ... ON CONFLICT, rather than catching in a hot loop.
Catch the condition you expect, by name or by code: WHEN unique_violation or WHEN SQLSTATE '23505', and a class name such as integrity_constraint_violation covers every code in its class. WHEN OTHERS also swallows real bugs such as integer overflow. RAISE EXCEPTION ... USING ERRCODE lets callers branch on a code, and in a handler SQLSTATE and SQLERRM describe what was caught.
A trigger function declares no arguments and returns type trigger; values listed in CREATE TRIGGER arrive in TG_ARGV. In PL/pgSQL it sees NEW and OLD, where NEW is null for DELETE and OLD is null for INSERT, plus TG_OP, TG_WHEN and TG_LEVEL naming the event. Reaching the end without RETURN is an error, so every path needs one.
From a row-level BEFORE trigger, return NEW to proceed, a modified NEW to change the row, or NULL to skip it silently; the statement still succeeds and later triggers do not fire for that row. A BEFORE DELETE trigger that returns NEW is a classic slip, because NEW is null there, so the delete reports DELETE 0. AFTER and statement-level triggers ignore their return value.
AD. Sessions, connections and server settings
SET lasts for the rest of the session once its transaction commits, but rolling that transaction back undoes it. SET LOCAL lasts only until the current transaction ends, either way, and outside a transaction block it only warns and changes nothing. Prefer SET LOCAL for one-off tuning such as work_mem, especially behind a pooler, where a plain SET leaks to whoever reuses the connection.
statement_timeout cancels any statement that runs past the limit, timed from its arrival at the server. Setting it in postgresql.conf affects every session, maintenance jobs included, which the manual advises against. Set it per role with ALTER ROLE app SET statement_timeout = '30s' or per transaction with SET LOCAL, and add lock_timeout to cap lock waits; PostgreSQL 17 also has transaction_timeout.
Each PostgreSQL connection is served by its own backend process, so every open client costs a process and its memory; a pooler such as PgBouncer shares a few server connections among many clients. Session pooling gives a client one connection until it leaves and supports everything. Transaction pooling reassigns it after each transaction, breaking SET, SQL-level PREPARE, session advisory locks, LISTEN and WITH HOLD cursors.
A temporary table lives in a schema private to your session, so no other session can see it and two sessions can each create one with the same name. It is dropped when the session ends, or at commit with ON COMMIT DROP, and while it exists it hides a permanent table of the same name. Autovacuum never analyzes it, so run ANALYZE yourself after filling it.
DECLARE c CURSOR FOR ... inside a transaction lets you call FETCH 1000 FROM c repeatedly instead of receiving the entire result at once, keeping client memory flat. A cursor without WITH HOLD disappears at commit, and declaring one outside a transaction block is an error. WITH HOLD survives the commit by materializing the result into a temporary file or memory at that point.
AE. Backup, restore and replication
pg_dump writes one database as SQL or an archive, consistent even under load, and loads into a newer major version; roles and tablespaces need pg_dumpall. A base backup copies the whole cluster's data directory, restores only to the same major version, and is the only kind WAL can roll forward. Use dumps for migration and selective restore, base backups for full recovery.
Each change is recorded in the WAL, and that record is flushed before the data page it describes is written. A commit therefore needs only one sequential log flush, while scattered table pages are written later. After a crash, replaying the log from the last checkpoint redoes whatever the pages missed, and the same stream feeds point-in-time recovery and replication.
Take a base backup, archive every WAL segment written after it, and you can restore the backup and replay the log up to any moment since, such as one second before a bad DELETE, named with recovery_target_time. Recovery covers the whole cluster, never a single table, and a missing segment in the archive stops replay at that point, so check the archive as carefully as the backup.
A physical standby replays the primary's WAL as it streams in, producing a block-for-block copy of the whole cluster that can serve read-only queries. It is asynchronous by default, so a commit can be lost if the primary fails before its WAL ships. Measure delay with write_lag, flush_lag and replay_lag in pg_stat_replication. Both servers must run the same major version.
Logical replication sends the row changes of the tables in a publication, so a subscriber can hold a subset, run a different major version, or carry its own indexes. In PostgreSQL 17 and 18 it does not replicate DDL, sequence values or large objects, so schema changes go to both sides by hand and sequences need resetting before a failover. UPDATE and DELETE need a replica identity, usually the primary key.
AF. Extensions and foreign data
CREATE EXTENSION name runs the extension's script in the current database and records every object it made, so DROP EXTENSION removes them together. A relocatable extension goes into the schema named by SCHEMA; if that schema is not on search_path, its operators are not found, and citext values then compare as case-sensitive text. ALTER EXTENSION name UPDATE moves to a newer installed version.
With a wrapper such as postgres_fdw you define a server, a user mapping that holds the remote credentials, and foreign tables, often in bulk with IMPORT FOREIGN SCHEMA. A single query can then join remote and local tables, but each access is a network round trip. Autovacuum never analyzes foreign tables, so run ANALYZE on them yourself or the planner works from guesses.
postgres_fdw sends a WHERE clause to the remote server only when its types, operators and functions are built-in or in an extension listed for the server, and IMMUTABLE, so a filter on now() runs locally after the rows have crossed the network. A join of two foreign tables on the same server can run remotely, but a join with a local table cannot. The Remote SQL line of EXPLAIN VERBOSE shows what actually travels.
The citext type lowers both sides when comparing, so =, LIKE and unique indexes ignore case without a lower() call at every site, and a unique citext column rejects a@x.com after A@x.com. Comparing it with a text value picks the case-sensitive text operator, so keep both sides citext. The PostgreSQL 17 manual suggests a nondeterministic collation as the more Unicode-aware alternative.
Keentune is not affiliated with or endorsed by the organizations whose documentation informs these maps.
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.