Re: psql JSON output format

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