Re: psql JSON output format

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Christoph Berg <myon(at)debian(dot)org>
Cc: Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Joe Conway <mail(at)joeconway(dot)com>
Subject: Re: psql JSON output format
Date: 2024-01-17 08:52:27
Message-ID: 8daf5f2f1691d806ab211432ab71045a07ee5df2.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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.
>
> 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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Maiquel Grassi 2024-01-17 08:52:33 RE: New Window Function: ROW_NUMBER_DESC() OVER() ?
Previous Message Shlok Kyal 2024-01-17 08:49:16 Re: speed up a logical replica setup