Re: psql JSON output format - Mailing list pgsql-hackers

From Laurenz Albe
Subject Re: psql JSON output format
Date
Msg-id 8daf5f2f1691d806ab211432ab71045a07ee5df2.camel@cybertec.at
Whole thread Raw
In response to Re: psql JSON output format  (Andrew Dunstan <andrew@dunslane.net>)
Responses Re: psql JSON output format
List pgsql-hackers
On Tue, 2024-01-16 at 11:49 -0500, Andrew Dunstan wrote:
> On 2024-01-16 Tu 11:07, Laurenz Albe wrote:
> > On Tue, 2024-01-09 at 16:51 +0000, Dean Rasheed wrote:
> > > On Tue, 9 Jan 2024 at 14:35, Christoph Berg <myon@debian.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.
>
> This is absolutely not true.
>
> CSV format with default settings is and has been from the beginning designed
> to be round trippable.

Sorry for being unclear.  I wasn't talking about COPY, but about the psql
output format:

CREATE TABLE xy (a integer, b text);

INSERT INTO xy VALUES (1, 'one'), (2, NULL), (3, '');

\pset format csv
Output format is csv.

TABLE xy;
a,b
1,one
2,
3,

Yours,
Laurenz Albe



pgsql-hackers by date:

Previous
From: Shlok Kyal
Date:
Subject: Re: speed up a logical replica setup
Next
From: Maiquel Grassi
Date:
Subject: RE: New Window Function: ROW_NUMBER_DESC() OVER() ?