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
