Re: psql JSON output format

On Wed, 2024-01-17 at 14:52 -0500, Robert Haas wrote:
> Let me start by clarifying that I'm OK with sacrificing
> round-trippability here as long as we do it thoughtfully.

Got you.

> I'm not quite sure that addresses all the issues, though. For
> instance, consider that 1.00::numeric and 1.0::numeric are equal but
> distinguishable. If those get rendered into the JSON unquoted as 1.00
> and 1.0, respectively, is that going to round-trip properly? What
> about float8 values where extra_float_digits=3 is needed to properly
> round trip? If we take PostgreSQL's array data types and turn them
> into JSON arrays, what happens with non-default bounds? I know how
> we're going to turn '{1,2}'::int[] into a JSON array, or at least I
> assume I do, but what in the world are we going to do about
> '[-3:-2]={1,2}'?
>
> As much as I think round-trippability is good, getting it to 100% here
> is probably a good bit of work.

I would go as far as saying that the attempt to preserve all that is
futile, if you are bound to JSON as format.

> 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 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.

I'd say that we should strive to preserve whatever information we
easily can, and we shouldn't worry about the rest.

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

Yours,
Laurenz Albe