MIME-Version: 1.0
References: <964a3377-d957-4a06-ad02-8228aefa65fc@dunslane.net> <fc61429f-76a1-4385-a2bd-999967da306f@manitou-mail.org>
In-Reply-To: <fc61429f-76a1-4385-a2bd-999967da306f@manitou-mail.org>
From: jian he <jian.universality@gmail.com>
Date: Thu, 19 Mar 2026 09:58:05 +0800
Message-ID: <CACJufxGAQk5cr3zcqX-PfN-9S+qnJgLL+OmNGhKeG_iG5mvddw@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: Daniel Verite <daniel@manitou-mail.org>
Cc: Andrew Dunstan <andrew@dunslane.net>, Masahiko Sawada <sawada.mshk@gmail.com>, 
	Joe Conway <mail@joeconway.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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CACJufxGAQk5cr3zcqX-PfN-9S%2BqnJgLL%2BOmNGhKeG_iG5mvddw%40mail.gmail.com>
Precedence: bulk

On Wed, Mar 18, 2026 at 10:37=E2=80=AFPM Daniel Verite <daniel@manitou-mail=
.org> wrote:
>
> Currently there's no difference in output between the null
> json value and the SQL null.
>
> postgres=3D# create table tbl  (j jsonb);
> postgres=3D# insert into tbl values('null');
> postgres=3D# insert into tbl values(null);
> postgres=3D# 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 =3D OidOutputFunctionCall(outfuncoid, val);
    appendStringInfoString(result, outputstr);
    pfree(outputstr);
    break;
```

Therefore I intended to document it as below:

  <refsect2 id=3D"sql-copy-json-format" xreflabel=3D"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?


--
jian
https://www.enterprisedb.com/