Re: psql JSON output format

Re: Laurenz Albe
> > But I do think it has positive
> > value. If we produce output that could be ingested back into PG later
> > with the right tool, that leaves the door open for someone to build
> > the tool later even if we don't have it today. If we produce output
> > that loses information, no tool built later can make up for the loss.

> I am all for losing as little information as possible, but I think
> that this discussion is going off on a tangent. After all, we are not
> talking about a data export tool here, we are talking about psql.

I've just posted the other patch where I need the JSON format:
https://www.postgresql.org/message-id/flat/Za6EfXeewwLRS_fs%40msg.df7cb.de

There, I need to be able to read back the query output into psql,
while at the same time being human-readable so the user can sanely
edit the data in an editor. The default "aligned" format is only
human-readable, while CSV is mostly only machine-readable. JSON is the
best option between the two, I think.

What I did now in v3 of this patch is to print boolean and numeric
values (ints, floats, numeric) without quotes, while adding the quotes
back to json. This solves the NULL vs 'null'::json problem.

> I don't see anybody complain that float8 values lose precision in
> the default aligned format, or that empty strings and NULL values
> look the same in aligned format. Why do the goalposts move for the
> JSON output format? I don't think psql output should be considered
> a form of backup.

Fwiw, not quoting numbers in JSON won't have any of these problems if
the JSON reader just passes the strings read through. (Which PG's JSON
parser does.)

> Can we get consensus that SQL NULL columns should be omitted from the
> output, and the rest left as it currently is?

I think that would be an interesting option for a JSON export format.
The psql JSON format is more for human inspection, where omitting the
columns might create confusion. (We could make it a pset parameter of
the format, but I think the default should be to show NULL columns.)

Christoph