public inbox for [email protected]
help / color / mirror / Atom feedRe: Emitting JSON to file using COPY TO
2+ messages / 2 participants
[nested] [flat]
* Re: Emitting JSON to file using COPY TO
@ 2026-03-20 12:41 Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Andrew Dunstan @ 2026-03-20 12:41 UTC (permalink / raw)
To: Joe Conway <[email protected]>; jian he <[email protected]>; Daniel Verite <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Junwang Zhao <[email protected]>; Florents Tselai <[email protected]>; Andrey M. Borodin <[email protected]>; Dean Rasheed <[email protected]>; Davin Shearer <[email protected]>; PostgreSQL development <[email protected]>
On 2026-03-19 Th 12:06 PM, Joe Conway wrote:
> 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
>
pushed with that addition.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Emitting JSON to file using COPY TO
@ 2026-03-20 12:58 Joe Conway <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Joe Conway @ 2026-03-20 12:58 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; jian he <[email protected]>; Daniel Verite <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Junwang Zhao <[email protected]>; Florents Tselai <[email protected]>; Andrey M. Borodin <[email protected]>; Dean Rasheed <[email protected]>; Davin Shearer <[email protected]>; PostgreSQL development <[email protected]>
On 3/20/26 08:41, Andrew Dunstan wrote:
>
> On 2026-03-19 Th 12:06 PM, Joe Conway wrote:
>> 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
>>
>
> pushed with that addition.
Awesome -- thanks for carrying that over the top!
--
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-03-20 12:58 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-20 12:41 Re: Emitting JSON to file using COPY TO Andrew Dunstan <[email protected]>
2026-03-20 12:58 ` Joe Conway <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox