{"id":43920,"date":"2026-04-15T06:41:59","date_gmt":"2026-04-15T04:41:59","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=43920"},"modified":"2026-04-14T06:44:47","modified_gmt":"2026-04-14T04:44:47","slug":"postgresql-19-json-format-for-copy-to","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-json-format-for-copy-to\/","title":{"rendered":"PostgreSQL 19: json format for “copy to”"},"content":{"rendered":"\n

PostgreSQL already has impressive support for working with data in json<\/a> format. If you look at the jsonb<\/a> data type and all the built-in functions and operators<\/a> you can use, there is so much you can do with it by default. Starting with PostgreSQL 19 there is one feature more when it comes to working with data in json format.<\/p>\n\n\n\n

“COPY<\/a>” already is quite powerful and the fastest way to get data in and out of PostgreSQL (you may read some previous posts about copy here<\/a>, here<\/a>, and here<\/a>). <\/p>\n\n\n\n

As usual lets start with a simple table:<\/p>\n\n\n

\npostgres=# create table t ( a int primary key, b text );\nCREATE TABLE\npostgres=# insert into t select i, md5(i::text) from generate_series(1,1000000) i;\nINSERT 0 1000000\n<\/pre><\/div>\n\n\n
To get that data out in text format you might simply do this:<\/p>\n\n\n
\npostgres=# copy t to '\/var\/tmp\/t';\nCOPY 1000000\npostgres=# \\! head \/var\/tmp\/t\n1       c4ca4238a0b923820dcc509a6f75849b\n2       c81e728d9d4c2f636f067f89cc14862c\n3       eccbc87e4b5ce2fe28308fd9f2a7baf3\n4       a87ff679a2f3e71d9181a67b7542122c\n5       e4da3b7fbbce2345d7772b0674a318d5\n6       1679091c5a880faf6fb5e6087eb1b2dc\n7       8f14e45fceea167a5a36dedd4bea2543\n8       c9f0f895fb98ab9159f51fd0297e236d\n9       45c48cce2e2d7fbdea1afc51c7c6ad26\n10      d3d9446802a44259755d38e6d163e820\n<\/pre><\/div>\n\n\n
Starting with PostgreSQL 19 you can do the same in json format:<\/p>\n\n\n
\npostgres=# copy t to '\/var\/tmp\/t1' with (format json);\nCOPY 1000000\npostgres=# \\! head \/var\/tmp\/t1\n{"a":1,"b":"c4ca4238a0b923820dcc509a6f75849b"}\n{"a":2,"b":"c81e728d9d4c2f636f067f89cc14862c"}\n{"a":3,"b":"eccbc87e4b5ce2fe28308fd9f2a7baf3"}\n{"a":4,"b":"a87ff679a2f3e71d9181a67b7542122c"}\n{"a":5,"b":"e4da3b7fbbce2345d7772b0674a318d5"}\n{"a":6,"b":"1679091c5a880faf6fb5e6087eb1b2dc"}\n{"a":7,"b":"8f14e45fceea167a5a36dedd4bea2543"}\n{"a":8,"b":"c9f0f895fb98ab9159f51fd0297e236d"}\n{"a":9,"b":"45c48cce2e2d7fbdea1afc51c7c6ad26"}\n{"a":10,"b":"d3d9446802a44259755d38e6d163e820"}\n<\/pre><\/div>\n\n\n
Specifying a SQL is also supported:<\/p>\n\n\n
\npostgres=# copy (select a from t) to '\/var\/tmp\/t1' with (format json);\nCOPY 1000000\npostgres=# \\! head \/var\/tmp\/t1\n{"a":1}\n{"a":2}\n{"a":3}\n{"a":4}\n{"a":5}\n{"a":6}\n{"a":7}\n{"a":8}\n{"a":9}\n{"a":10}\n<\/pre><\/div>\n\n\n
As noted in the commit message<\/a> there are some options which are not compatible with the json format:<\/p>\n\n\n\n