"PostgreSQL 19 COPY TO JSON: Stream Tables to NDJSON"
PostgreSQL 19 copy to json adds native JSON output to COPY: one object per row (NDJSON) by default, FORCE_ARRAY for a single array. Runnable export pipeline examples.
Exporting a Postgres table to JSON has always meant workarounds: row_to_json() in a SELECT, json_agg() with a memory cliff, or a cursor loop in application code. PostgreSQL 19 copy to json ends that. COPY TO finally emits JSON natively — one object per row (NDJSON) by default, or a single JSON array with FORCE_ARRAY . It's streaming, so it handles tables that would never fit in memory, and it's faster than the equivalent SELECT-based pipelines. Beta status: PostgreSQL 19 is still in beta — beta 4 shipped 2026-09-24, RC1 is scheduled 2026-10-15, GA scheduled 2026-10-29. JSON output for COPY TO survived the beta 4 revert wave (it's shipping), but don't run beta Postgres in production. Syntax and behavior below are per the beta; re-check the GA release notes after 2026-10-29. Last updated: October 4, 2026 What you'll learn COPY ... TO STDOUT WITH (FORMAT JSON) — the new syntax and NDJSON default FORCE_ARRAY for single-array output Column lists and exporting arbitrary queries, not just tables How this replaces row_to_json() / json_agg() pipelines (and why it's faster) The incompatibilities: COPY FROM, HEADER, NULL, DELIMITER, and friends Streaming large exports without blowing up memory The Old Way: row_to_json() Pipelines Before looking at the new syntax, remember what the workaround looked like — because its failure modes are exactly what COPY TO JSON fixes: -- The old way: works, but materializes everything SELECT row_to_json(t) FROM (SELECT * FROM orders) t; For a real export you'd wrap this in \copy or a client loop. The problems: json_agg() explodes memory. SELECT json_agg(row_to_json(t)) FROM (SELECT * FROM huge_table) t builds the entire result as one JSON value in memory. On a 50 GB table, the backend dies or your client OOMs.…