Re: psql JSON output format

On Tue, 2024-01-09 at 16:51 +0000, Dean Rasheed wrote:
> On Tue, 9 Jan 2024 at 14:35, Christoph Berg <myon(at)debian(dot)org> wrote:
> >
> > Getting it print numeric/boolean without quotes was actually easy, as
> > well as json(b). Implemented as the attached v2 patch.
> >
> > But: not quoting json means that NULL and 'null'::json will both be
> > rendered as 'null'. That strikes me as a pretty undesirable conflict.
> > Does the COPY patch also do that?
>
> Yes. Perhaps what needs to happen is for a NULL column to be omitted
> entirely from the output. I think the COPY TO json patch would have to
> do that if COPY FROM json were to be added later, to make it
> round-trip safe.

I think the behavior is fine as it is. I'd expect both NULL and JSON "null"
to be rendered as "null". I think the main use case for a feature like this
is people who need the result in JSON for further processing somewhere else.

"Round-trip safety" is not so important. If you want to move data from
PostgreSQL to PostgreSQL, you use the plain or the binary format.
The CSV format by default renders NULL and empty strings identical, and
I don't think anybody objects to that.

Yours,
Laurenz Albe