Message-ID: <2732ec01-112c-40ae-8b17-a6df5f30b266@dunslane.net>
Date: Fri, 20 Mar 2026 08:41:55 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Emitting JSON to file using COPY TO
To: Joe Conway <mail@joeconway.com>, 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>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: <90984087-723c-48e6-88ca-661ed15b2dd6@joeconway.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/2732ec01-112c-40ae-8b17-a6df5f30b266%40dunslane.net>
Precedence: bulk


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.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com