Re: New "single" COPY format

On 2024-12-16 Mo 10:09 AM, Joel Jacobson wrote:
> Hi hackers,
>
> After further consideration, I'm withdrawing the patch.
> Some fundamental questions remain unresolved:
>
> - Should round-trip fidelity be a strict goal? By "round-trip fidelity",
> I mean that data exported and then re-imported should yield exactly
> the original values, including the distinction between NULL and empty strings.
> - If round-trip fidelity is a requirement, how do we distinguish NULL from empty
> strings without delimiters or escapes?
> - Is automatic newline detection (as in "csv" and "text") more valuable than
> the ability to embed \r (CR) characters?
> - Would it be better to extend the existing COPY options rather than introducing
> a new format?
> - Or should we consider a JSONL format instead, one that avoids the NULL/empty
> string problem entirely?
>
> No clear solution or consensus has emerged. For now, I'll step back from the
> proposal. If someone wants to revisit this later, I'd be happy to contribute.
>
> Thanks again for all the feedback and consideration.
>

We seem to have got seriously into the weeds, here. I'd be sorry to see
this dropped. After all, it's not something new, and while we have a
sort of workaround for "one json doc per line" it's far from obvious,
and except in a few blog posts undocumented.

I think we're trying to be far too general here but in the absence of
more general use cases. The ones I recall having encountered in the wild
are:

  . one json datum per line

  . one json document per file

  . a sequence of json documents per file

The last one is hard to deal with, and I think I've only seen it once or
twice, so I suggest leaving it aside for now.

Notice these are all JSON. I could imagine XML might have similar
requirements, but I encounter it extremely rarely.

Regarding NULL, an empty string is not a valid JSON literal, so there
should be no confusion there. It is valid for XML, though.

Given all that I think restricting ourselves to just the JSON cases, and
possibly just to JSONL, would be perfectly reasonable.

Regarding CR, it's not a valid character in a JSON string item, although
it is valid in JSON whitespace. I would not treat it as magical unless
it immediately precedes an NL. That gives rise to a very sight
ambiguity, but I think it's one we could live with.

As for what the format is called, I don't like the "LIST" proposal much,
even for the general case. Seems too close to an array.

cheers

andrew

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