public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joe Conway <[email protected]>
To: Andrew Dunstan <[email protected]>
To: jian he <[email protected]>
To: Daniel Verite <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Junwang Zhao <[email protected]>
Cc: Florents Tselai <[email protected]>
Cc: Andrey M. Borodin <[email protected]>
Cc: Dean Rasheed <[email protected]>
Cc: Davin Shearer <[email protected]>
Cc: PostgreSQL development <[email protected]>
Subject: Re: Emitting JSON to file using COPY TO
Date: Thu, 19 Mar 2026 12:06:13 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CACJufxGAQk5cr3zcqX-PfN-9S+qnJgLL+OmNGhKeG_iG5mvddw@mail.gmail.com>
<[email protected]>
On 3/19/26 11:02, Andrew Dunstan wrote:
>
> On 2026-03-18 We 9:58 PM, jian he wrote:
>> On Wed, Mar 18, 2026 at 10:37 PM Daniel Verite <[email protected]> wrote:
>>> Currently there's no difference in output between the null
>>> json value and the SQL null.
>>>
>>> postgres=# create table tbl (j jsonb);
>>> postgres=# insert into tbl values('null');
>>> postgres=# insert into tbl values(null);
>>> postgres=# copy tbl to stdout with (format json);
>>> {"j":null}
>>> {"j":null}
>>>
>>> Does it have to be that way or are there valid distinct outputs
>>> that we could use to avoid this ambiguity?
>>>
>> This is an existing (quite old) behavior of
>> composite_to_json->datum_to_json_internal, IMHO.
>>
>> ```
>> if (is_null)
>> {
>> appendBinaryStringInfo(result, "null", strlen("null"));
>> return;
>> }
>> ```
>> produce the same results as
>> ```
>> case JSONTYPE_JSON:
>> /* JSON and JSONB output will already be escaped */
>> outputstr = OidOutputFunctionCall(outfuncoid, val);
>> appendStringInfoString(result, outputstr);
>> pfree(outputstr);
>> break;
>> ```
>>
>> Therefore I intended to document it as below:
>>
>> <refsect2 id="sql-copy-json-format" xreflabel="JSON Format">
>> <title>JSON Format</title>
>> <para>
>> When the <literal>json</literal> format is used, data is
>> exported with one JSON object per line,
>> where each line corresponds to a single record.
>> The <literal>json</literal> format has no standard way to
>> distinguish between an SQL <literal>NULL</literal> and a JSON
>> <literal>null</literal> literal.
>> In the examples that follow, the following table containing JSON
>> data will be used:
>> <programlisting>
>> CREATE TABLE my_test (a jsonb, b int);
>> INSERT INTO my_test VALUES ('null', 1), (NULL, 1);
>> </programlisting>
>>
>> When exporting this table using the <literal>json</literal> format:
>> <programlisting>
>> COPY my_test TO STDOUT (FORMAT JSON);
>> </programlisting>
>> In the resulting output, both the SQL <literal>NULL</literal> and
>> the JSON <literal>null</literal> are rendered identically:
>> <screen>
>> {"a":null,"b":1}
>> {"a":null,"b":1}
>> </screen>
>> </para>
>> </refsect2>
>>
>>
>>
>> what do you think?
>>
>>
>>
>
> I can live with that, if others can.
+1
WFM
--
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Emitting JSON to file using COPY TO
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox