PostgreSQL 19 - new SQL surface and performance changes

Not everything in a major release is a headline. This page collects the SQL additions that will show up in application code once 19 ships - new clauses, new COPY behaviour, and one planner change (NOT IN) that is invisible until you read the plan. The I/O side of the release has its own page in asynchronous I/O; the smaller executor speedups are summarised on the overview. Everything below is from the beta documentation.

INSERT … ON CONFLICT DO SELECT

ON CONFLICT used to have two outcomes: DO NOTHING (skip silently) or DO UPDATE (write the conflict away). 19 adds DO SELECT: return the conflicting row, optionally locking it, without modifying it:

INSERT INTO accounts (id, balance) VALUES (42, 100)
ON CONFLICT (id) DO SELECT FOR UPDATE
RETURNING *;

If the insert succeeds you get the new row; if it conflicts you get the existing row under a FOR UPDATE lock (NO KEY UPDATE, SHARE and KEY SHARE are also accepted), and a WHERE condition can filter which conflicts are returned at all. This is the “upsert-or-read” round trip collapsed into one statement - an idempotent “get or create”. Privileges: DO SELECT needs SELECT on the table; adding FOR UPDATE/SHARE needs UPDATE on at least one column too.

Window functions: IGNORE NULLS

IGNORE NULLS and RESPECT NULLS are now supported on lead(), lag(), first_value(), last_value() and nth_value():

SELECT ts,
       last_value(price) IGNORE NULLS
         OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_known
FROM quotes;

IGNORE NULLS answers “the most recent non-null value”, which previously took a subquery or a FILTER workaround. Default remains RESPECT NULLS.

COPY grows up

Four changes to COPY in 19:

• JSON output: COPY ... TO (FORMAT JSON) emits one JSON object per row; FORCE_ARRAY wraps the whole result in a single JSON array. JSON is output-only (COPY TO). Caveat documented: a SQL NULL and a JSON column containing the literal null are indistinguishable in the output.
• Bad input to NULL: COPY FROM ... (ON_ERROR SET_NULL) stores NULL for invalid input values instead of failing or skipping - distinct from ON_ERROR ignore, which skips the row.
• Multi-line headers: HEADER on COPY FROM accepts a non-negative integer, discarding that many lines; HEADER MATCH requires the first line to name the table’s columns exactly, in order. Previously only a single header line could be skipped.
• Partitioned tables: COPY TO can now dump a partitioned table directly instead of needing COPY (SELECT ...). The release notes note this also speeds up logical replication table sync. One restriction to know: COPY FROM ... WHERE can no longer reference system columns (xmin, ctid) - their values were never well-defined there.

COPY (SELECT * FROM events WHERE ts >= '2026-01-01') TO '/tmp/events.json' (FORMAT JSON, FORCE_ARRAY);
COPY measurements TO '/tmp/measurements.csv' (HEADER);           -- partitioned table, directly
COPY staging FROM '/tmp/dirty.csv' (FORMAT CSV, HEADER 2, ON_ERROR SET_NULL);

Smaller SQL and type additions

The planner change hidden in your WHERE clause

NOT IN can now be planned as an ANTI JOIN when the planner can prove NULLs cannot occur - more LEFT JOINs convert to anti joins too, and Memoize applies to anti joins with unique inner sides. The caveat deserves emphasis: the transform fires only when NULLs are not present, which is exactly the case where NOT IN already returns surprising results. NOT EXISTS remains the robust formulation; the transform removes the performance argument against it rather than the correctness one.

The rest of 19’s performance work - faster foreign-key checks, radix sort, SIMD COPY FROM parsing, targeted NOTIFY wakeups, scans setting pages all-visible - is summarised in the overview’s performance section, and the I/O worker autoscaling plus EXPLAIN (ANALYZE, IO) is on the asynchronous I/O page.

What changed, and in which release

Where it breaks

• ON CONFLICT DO SELECT returning fewer rows than expected - a row locked but filtered out by the WHERE condition is not returned; check RETURNING output rather than assuming row count.
• JSON COPY ambiguity - 'null'::json and SQL NULL produce identical output; do not ingest the JSON back expecting to distinguish them.
• ON_ERROR SET_NULL masking data problems - invalid input becomes NULL silently; pair it with REJECT_LIMIT or a post-load audit on nullable columns.
• Beta caveat - all syntax above is from the 19 beta documentation.

What this is not for

• Replacing NOT EXISTS defensively - the anti-join transform only fires when the planner can prove no NULLs; keep writing NOT EXISTS where correctness matters regardless.
• JSON export as a stable interchange format - COPY ... FORMAT JSON loses the null-versus-JSON-null distinction; use a SELECT to JSON function composition if you need explicit null markers.