Re: Emitting JSON to file using COPY TO

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(at)manitou-mail(dot)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