Re: psql JSON output format

On 2024-01-17 We 03:52, Laurenz Albe wrote:
> 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,
>

I think the reason nobody's complained about it is quite possibly that
very few people have used it. That's certainly the case with me - if I'd
noticed it I would have complained.

cheers

andrew

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