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