PostgreSQL already has impressive support for working with data in json format. If you look at the jsonb data type and all the built-in functions and operators 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.

“COPY” 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, here, and here).

As usual lets start with a simple table:

postgres=# create table t ( a int primary key, b text );
CREATE TABLE
postgres=# insert into t select i, md5(i::text) from generate_series(1,1000000) i;
INSERT 0 1000000

To get that data out in text format you might simply do this:

postgres=# copy t to '/var/tmp/t';
COPY 1000000
postgres=# \! head /var/tmp/t
1       c4ca4238a0b923820dcc509a6f75849b
2       c81e728d9d4c2f636f067f89cc14862c
3       eccbc87e4b5ce2fe28308fd9f2a7baf3
4       a87ff679a2f3e71d9181a67b7542122c
5       e4da3b7fbbce2345d7772b0674a318d5
6       1679091c5a880faf6fb5e6087eb1b2dc
7       8f14e45fceea167a5a36dedd4bea2543
8       c9f0f895fb98ab9159f51fd0297e236d
9       45c48cce2e2d7fbdea1afc51c7c6ad26
10      d3d9446802a44259755d38e6d163e820

Starting with PostgreSQL 19 you can do the same in json format:

postgres=# copy t to '/var/tmp/t1' with (format json);
COPY 1000000
postgres=# \! head /var/tmp/t1
{"a":1,"b":"c4ca4238a0b923820dcc509a6f75849b"}
{"a":2,"b":"c81e728d9d4c2f636f067f89cc14862c"}
{"a":3,"b":"eccbc87e4b5ce2fe28308fd9f2a7baf3"}
{"a":4,"b":"a87ff679a2f3e71d9181a67b7542122c"}
{"a":5,"b":"e4da3b7fbbce2345d7772b0674a318d5"}
{"a":6,"b":"1679091c5a880faf6fb5e6087eb1b2dc"}
{"a":7,"b":"8f14e45fceea167a5a36dedd4bea2543"}
{"a":8,"b":"c9f0f895fb98ab9159f51fd0297e236d"}
{"a":9,"b":"45c48cce2e2d7fbdea1afc51c7c6ad26"}
{"a":10,"b":"d3d9446802a44259755d38e6d163e820"}

Specifying a SQL is also supported:

postgres=# copy (select a from t) to '/var/tmp/t1' with (format json);
COPY 1000000
postgres=# \! head /var/tmp/t1
{"a":1}
{"a":2}
{"a":3}
{"a":4}
{"a":5}
{"a":6}
{"a":7}
{"a":8}
{"a":9}
{"a":10}

As noted in the commit message there are some options which are not compatible with the json format:

  • HEADER
  • DEFAULT
  • NULL
  • DELIMITER
  • FORCE QUOTE
  • FORCE NOT NULL
  • and FORCE NULL

Also not supported (currently) is “copy from”.

Thumbnail [60x60]
by
Daniel Westermann