Horton

Re: Emitting JSON to file using COPY TO

Daniel Verite <daniel@manitou-mail.org>

View thread View on postgr.es
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Andrew Dunstan" <andrew@dunslane.net>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Jian He <jian.universality@gmail.com>, Joe Conway <mail@joeconway.com>, Junwang Zhao <zhjwpku@gmail.com>, Florents Tselai <florents.tselai@gmail.com>, Andrey M. Borodin <x4mmm@yandex-team.ru>, Dean Rasheed <dean.a.rasheed@gmail.com>, Davin Shearer <davin@apache.org>, PostgreSQL development <pgsql-hackers@postgresql.org>
Date: 2026-03-18T14:37:23Z
Lists: pgsql-hackers, pgsql-general 
	Andrew Dunstan wrote:

> Here's a v30 set that I hope fixes these issues.

Currently there's no difference in output between the null
json value and the SQL null.

postgres=# create table tbl  (j jsonb);
CREATE TABLE

postgres=# insert into tbl values('null');
INSERT 0 1

postgres=# insert into tbl values(null);
INSERT 0 1

postgres=# table tbl;
  j   
------
 null
 
(2 rows)

postgres=# copy tbl to stdout with (format json);
{"j":null}
{"j":null}

If we had to reload this file, we could not determine which
kind of null we had even though they are different at the SQL
level:

postgres=# select null::jsonb is distinct from 'null'::jsonb;
 ?column? 
----------
 t

Does it have to be that way or are there valid distinct outputs
that  we could use to avoid this ambiguity?


Best regards,
-- 
Daniel Vérité 
https://postgresql.verite.pro/

Commits

  1. Add option force_array for COPY JSON FORMAT

  2. json format for COPY TO

  3. introduce CopyFormat, refactor CopyFormatOptions

  4. Doc: add IDs to copy.sgml's <varlistentry> and <refsect1>

  5. Refactor COPY TO to use format callback functions.