Message-ID: <373fe053-9a46-4d24-8c51-f4e46a79aa52@joeconway.com>
Date: Fri, 20 Mar 2026 08:58:45 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Emitting JSON to file using COPY TO
To: Andrew Dunstan <andrew@dunslane.net>,
 jian he <jian.universality@gmail.com>,
 Daniel Verite <daniel@manitou-mail.org>
Cc: Masahiko Sawada <sawada.mshk@gmail.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>
References: <964a3377-d957-4a06-ad02-8228aefa65fc@dunslane.net>
 <fc61429f-76a1-4385-a2bd-999967da306f@manitou-mail.org>
 <CACJufxGAQk5cr3zcqX-PfN-9S+qnJgLL+OmNGhKeG_iG5mvddw@mail.gmail.com>
 <8428eb0f-f467-4cbd-a510-5be4f29b9ad7@dunslane.net>
 <90984087-723c-48e6-88ca-661ed15b2dd6@joeconway.com>
 <2732ec01-112c-40ae-8b17-a6df5f30b266@dunslane.net>
Content-Language: en-US
From: Joe Conway <mail@joeconway.com>
In-Reply-To: <2732ec01-112c-40ae-8b17-a6df5f30b266@dunslane.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/373fe053-9a46-4d24-8c51-f4e46a79aa52%40joeconway.com>
Precedence: bulk

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 
>>>> <daniel@manitou-mail.org> 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