Re: psql JSON output format

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. The docs say about CSV format:

A NULL is output as the NULL parameter string and is not quoted,
while a non-NULL value matching the NULL parameter string is quoted.
For example, with the default settings, a NULL is written as an
unquoted empty string, while an empty string data value is written
with double quotes ("").

CSV format with default settings is and has been from the beginning
designed to be round trippable.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com