PG19 Hacktober: 31 Days of New Features: COPY Gets a Big Upgrade: JSON Output, ON_ERROR SET_NULL, Multi-Line Headers and Partitioned Tables

COPY is the oldest and still the fastest bulk path into and out of PostgreSQL. It rarely makes headlines, but PostgreSQL 19 changes it in four ways that remove real day-to-day friction:

FORMAT JSON for COPY TO, with an optional FORCE_ARRAY.
ON_ERROR set_null for COPY FROM, a gentler cousin of ignore.
HEADER <integer>, which skips an arbitrary number of leading lines.
Direct COPY TO on partitioned tables, with no more COPY (SELECT …) workaround.

Because file_fdw reuses the COPY FROM machinery, several of these also show up as foreign-table options. This post covers each feature, the semantics that are easy to get wrong, and a few patterns that combine them.

Version note. Everything here is taken from PostgreSQL 19 at RC1 (October 2026) and the associated commit discussions. Behaviour can still shift before GA, so re-check the release notes. Examples are illustrative; run them on your own 19 build before relying on exact output text.

1. JSON output for COPY TO

What it does

Until now, exporting JSON from Postgres meant wrapping rows in to_json() or row_to_json() inside a SELECT and then abusing COPY ... (FORMAT text) or psql -At to get the result out, fighting escaping along the way. In 19 you can write:

COPY users TO STDOUT (FORMAT json);
COPY (SELECT id, email FROM users WHERE active) TO '/tmp/active_users.json' (FORMAT json);
COPY users (email, name) TO STDOUT (FORMAT json);

By default the output is newline-delimited: one JSON object per row, keyed by column name. This is the NDJSON style that log pipelines, jq, DuckDB, BigQuery loaders and most streaming tools read natively.

{"id":1,"email":"ada@example.com"}
{"id":2,"email":"alan@example.com"}

The work is streamed row by row. You do not need a json_agg() that materialises the whole result in memory, and there is no per-row wrapper function call in your SQL.

FORCE_ARRAY: a single JSON document

Some consumers want one valid JSON document rather than NDJSON. FORCE_ARRAY wraps the output in [ and ] and puts commas between rows:

COPY (SELECT * FROM (VALUES (1),(2)) val(id))
  TO STDOUT (FORMAT json, FORCE_ARRAY);

[
{"id":1}
,{"id":2}
]

Note the layout: the leading comma form keeps each row on its own line, so the file is still friendly to line-oriented tools such as grep and diff, even though it is a valid array. FORCE_ARRAY is allowed only with COPY TO and FORMAT json, and defaults to false.

Restrictions you need to know

JSON is not just “another text-like format.” Per the docs:

COPY TO only. COPY FROM with FORMAT json is rejected. Loading JSON still means staging it into a jsonb column (more on this below).
No DELIMITER, NULL, DEFAULT or HEADER. Those options make no sense when the structure is defined by JSON itself, and are rejected with json (as they are with binary).
ON_ERROR, REJECT_LIMIT and the CSV-only options (QUOTE, ESCAPE, FORCE_QUOTE and friends) do not apply either.

The null ambiguity

This is the one semantic trap the docs call out explicitly. A SQL NULL is written as JSON null. But a json or jsonb column holding the JSON literal null is also written as null, so the two cases collapse:

COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j))
  TO STDOUT (FORMAT json);
{"j":null}
{"j":null}

If the distinction matters downstream (for example, “unset” versus “explicitly null” in a document column), emit it explicitly:

COPY (SELECT id,
            j,
            j IS NULL AS j_is_sql_null
      FROM docs) TO STDOUT (FORMAT json);

Where it fits, and where it does not

Good fits

Feeding NDJSON to log/analytics tooling or object storage without a custom exporter.
COPY ... TO PROGRAM pipelines, for example COPY t TO PROGRAM 'gzip > /data/t.ndjson.gz' (FORMAT json). (PROGRAM needs superuser or pg_execute_server_program, and the command runs through the server’s shell.)
Client-side exports with \copy from psql, where file permissions follow the client rather than the server.

Not a fit (yet)

Round-tripping. Since there is no COPY FROM ... (FORMAT json), you still stage with a jsonb column and expand with jsonb_populate_record or similar. If you pipe NDJSON through a text-format COPY FROM into a single jsonb column, remember that text format treats backslashes as escapes, which collides with JSON's own escaping. Pick a delimiter/escape combination that cannot occur in your data, or load through a client library that can send each document as a parameter. Test this with your real data.
Type fidelity. JSON has no native representation for many Postgres types (numeric precision, bytea, intervals). Values come out as whatever the type’s JSON conversion produces, so check anything consumed by a strict schema.

2. ON_ERROR set_null

Background: from stop to ignore to set_null

Postgres 17 introduced ON_ERROR, with stop (the long-standing default) and ignore. Version 18 added LOG_VERBOSITY and REJECT_LIMIT to make ignore more controllable. Version 19 adds a third action:

ON_ERROR	What happens when a field cannot be converted to its column type
stop (default)	The whole COPY fails
ignore	The entire input row is discarded; processing continues
set_null	Only the bad field becomes NULL; the row is still loaded

(The odd spelling set_null is deliberate. The patch discussion noted that the grammar could not accept a bare NULL there, so set_null was chosen.)

A worked example

CREATE TABLE sales (
  id       int,
  amount   numeric(10,2),
  sold_on  date,
  region   text
);
/tmp/sales.csv:
id,amount,sold_on,region
1,19.99,2026-01-05,EU
2,abc,2026-01-06,US
3,7.50,not-a-date,EU
COPY sales FROM '/tmp/sales.csv'
  (FORMAT csv, HEADER 1, ON_ERROR set_null, LOG_VERBOSITY verbose);

The result is three rows. Row 2 has amount = NULL, row 3 has sold_on = NULL, and the rest of each row survives. With ignore, you would have kept only row 1. With stop, you would have kept nothing.

Rules and edge cases

Text and CSV only. ignore and set_null apply only to COPY FROM with FORMAT text or CSV.
Only conversion errors are covered. ON_ERROR handles failures converting an input value into the column’s data type. A wrong number of fields on a line, constraint violations raised by triggers, or CHECK failures are not turned into nulls. Expect those to keep failing the command.
Nulls must be legal. If the target column cannot hold a null, set_null cannot rescue it. For a domain that forbids nulls, the commit adds a dedicated error that says ON_ERROR SET_NULL cannot be applied because the column’s domain does not accept nulls. For a plain NOT NULL column, expect the usual not-null violation.
REJECT_LIMIT does not apply. The docs state that REJECT_LIMIT must be used with ON_ERROR = ignore. So set_null has no built-in error budget: a completely garbage column will be silently turned into all-NULLs.
Reporting. If at least one row was affected, a NOTICE with a count is emitted at the end. With LOG_VERBOSITY verbose, you get one NOTICE per failure that identifies the input line and column. With silent, nothing is reported.

The design trade-off: lossy by construction.

ignore drops a row you can later find by diffing counts. set_null produces a row that looks complete but has had information destroyed, and in the table it is indistinguishable from a legitimately null value. That makes it a good tool for exploratory loads and best-effort ingestion, and a risky one for anything with correctness guarantees.

Practical guardrails:

Compare counts of nulls before and after for important columns. A sudden jump in NULL rate is the only built-in signal you get for a column that is systematically malformed.
Capture the NOTICEs (LOG_VERBOSITY verbose, and client or server logs) for audit if you need to know which lines changed.
Prefer a staging pattern for critical data: load into an all-text table, validate with SQL, then insert into the typed table. set_null is a convenience, not a replacement for validation.

3. Multi-line headers: HEADER <integer>

Real-world files rarely start with a single clean header line. ERP exports prepend banners, report tools add timestamps, and some vendors put units on a second line. Before 19, HEADER was effectively “skip the first line or don't.” Now, on input, it accepts a non-negative integer:

# Exported by ERP v4
# 2026-10-01 00:00 UTC
id,amount,sold_on,region
1,19.99,2026-01-05,EU
COPY sales FROM '/tmp/sales_banner.csv' (FORMAT csv, HEADER 3);

This discards the first three lines (two banner lines and the column header) and loads the rest.

Semantics

HEADER value	COPY FROM	COPY TO
true / on / 1	Skip first line	Write a header line
false / off / 0	No header	No header
integer N (non-negative)	`Skip N lines`	Not allowed (only 0 and 1 are accepted as booleans)
match	Skip the first line, and it must exactly match the table’s column names (count and order)	Not allowed

A few consequences worth spelling out:

HEADER N is a skipper, not a validator. It does not check the column names. Only MATCH does, and MATCH covers only a single first line. If you have a three-line preamble and want to verify the column row, you currently have to choose: skip three, or pre-process the file.
Lines are not necessarily records. In CSV, a quoted field can contain a newline, so “lines” and “rows” can differ. The docs describe the integer as a number of lines, so verify how your build counts if a banner could contain quoted newlines.
Not for binary or json. The HEADER option is rejected for both.
Counts of skipped lines are on you. If a vendor changes the preamble length, HEADER 3 will quietly start eating a data row. Pair it with a row count check or WHERE filter.

4. COPY TO on partitioned tables

If you have ever tried to dump a partitioned table with plain COPY, you know the error:

ERROR:  cannot copy from partitioned table "plans"
HINT:  Try the COPY (SELECT ...) TO variant.

The workaround, COPY (SELECT * FROM plans) TO …, worked, but it was an irritating special case. It was also a documented gap, since COPY FROM into partitioned tables has been supported (with multi-insert batching) since version 12.

Committed by Masahiko Sawada in October 2025, PostgreSQL 19 lets you write:

COPY plans TO '/tmp/plans.copy';

What rows you get

The docs define the semantics precisely, and the distinction matters:

For a non-partitioned table, COPY t copies the same rows as SELECT * FROM ONLY t. The ONLY is relevant to legacy inheritance: child-table rows are not included.
For a partitioned table, COPY t copies the same rows as SELECT * FROM t, meaning all partitions.
Inheritance child tables and views are still not directly supported. Use COPY (SELECT ...) for those.
Row-level security on the partitioned table applies the same way it does for COPY TO on a plain table (SELECT policies are enforced).

Performance

The commit message argues this is faster than the COPY (SELECT …) form because it avoids query processing and passing tuples through the executor to the COPY machinery. In an informal test on a table of roughly 1.8 million rows, depesz measured about 45.9 seconds for the direct form versus 45.4 seconds for the SELECT form, which is effectively parity. Treat the gain as workload-dependent; the main win is usability: scripts, pg_dump-like tooling, and ORMs no longer need a special case.

Gotchas

COPY FREEZE is still not possible on a partitioned table (it is a COPY FROM option anyway).
Partition-wise exports remain useful: COPY plans_part_13 TO … still dumps a single partition, and is the right tool for parallelising a large export across several sessions.
Combining this with FORMAT json works the same as for any other table, so a partitioned table can now be streamed out as NDJSON in one command.

5. file_fdw inherits the new input options

file_fdw reads files using COPY FROM’s parser, and its options mirror COPY’s. In 19, the documented option list includes header (“whether to skip a header line, or how many header lines to skip”), on_error, reject_limit, and log_verbosity. The ON_ERROR set_null change also made set_null usable on file_fdw tables, and a follow-up patch added regression coverage for it.

CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE sales_raw (
  id      int,
  amount  numeric(10,2),
  sold_on date,
  region  text
) SERVER files
OPTIONS (
  filename   '/data/sales_banner.csv',
  format     'csv',
  header     '3',
  on_error   'set_null',
  log_verbosity 'silent'
);
SELECT count(*) AS rows,
      count(*) FILTER (WHERE amount  IS NULL) AS null_amounts,
      count(*) FILTER (WHERE sold_on IS NULL) AS null_dates
FROM sales_raw;

That turns “how dirty is this vendor file?” into a one-line SQL query, with no load step.

Things to remember from the file_fdw docs:

Foreign-table option syntax needs a value for every option, so write header ‘true’ rather than a bare header.
FORCE_QUOTE is not supported by file_fdw, and since access is read-only, this is expected. force_null and force_not_null are available as column options.
Changing filename or program requires superuser or the pg_read_server_files / pg_execute_server_program roles. Treat program like COPY ... PROGRAM: it is run by the shell, so keep untrusted input out of it.
Since the JSON format is COPY TO only, you cannot point file_fdw at an NDJSON file with format ‘json’.
Foreign tables are re-read on every scan, so each query re-parses the file and re-applies set_null. Counts of NOTICEs are per scan.

Putting it together

Recipe A: archive a partitioned table as compressed NDJSON

COPY events TO PROGRAM 'gzip > /archive/events_2026_10.ndjson.gz'
  (FORMAT json);

Before 19 this needed a COPY (SELECT row_to_json(e) FROM events e) with careful escaping, and, for the partitioned table, the SELECT wrapper anyway.

Recipe B: tolerant ingestion of a messy vendor file

COPY staging_sales FROM '/in/vendor.csv'
  (FORMAT csv, HEADER 3, ON_ERROR set_null, LOG_VERBOSITY verbose);

Then reconcile with a control total from the vendor, and look at NOTICE output for which lines were altered.

Recipe C: strict ingestion with a controlled error budget

COPY sales FROM '/in/sales.csv'
  (FORMAT csv, HEADER MATCH, ON_ERROR ignore, REJECT_LIMIT 100);

Here, ignore plus REJECT_LIMIT aborts when the file is worse than expected. This is the option to pick when you want failure to be loud. set_null cannot offer this.

Choosing the right ON_ERROR mode

You want…	Use
Any bad value to fail the load	stop (default)
Skip unusable rows and cap how many	ignore + REJECT_LIMIT
Keep every row; blank only broken fields	set_null
Full auditability	Load to a text staging table, then cast

Upgrade checklist

Pre-19 clients and tools that parse COPY option errors will see new accepted values; nothing existing changes meaning.
If you wrap COPY in tooling that enumerates formats, add json for output only.
Replace COPY (SELECT * FROM partitioned) TO where you want the simpler form, but keep the SELECT form if you need filtering or inheritance children.
Audit any pipeline that adopts set_null: add null-rate monitoring.
Re-check the final release notes: this post is based on the RC1 documentation.

Summary

None of these four changes is flashy on its own, but together they make COPY fit modern data plumbing much better: JSON out of the box, tolerant ingestion that can salvage partially bad rows, honest handling of preambles, and no more special case for partitioned tables. The main discipline set_null demands is remembering that it trades visibility for convenience.