Re: New "single" COPY format

On Sat, Dec 21, 2024 at 1:57 AM Joel Jacobson <joel(at)compiler(dot)org> wrote:

> How about:
>
> COPY table_name [ ( column_name ) ] { FROM | TO } 'filename' (FORMAT
> jsonb);
>
> - If column list is omitted, table_name must have exactly one column.
> - If column list is specified, it must be of length one.
> - The column type must be jsonb.
> - Each line is a single jsonb value; no multi-line json permitted.
> - Non-LF whitespace, i.e. [ \r\t], are allowed anywhere where
> whitespace can exist according to the json spec, but are discarded
> upon import, since jsonb doesn't store whitepsace.
> - The LF character, i.e. [\n], determine the end of a jsonb value.
> - Empty line are imported as NULL::jsonb values
> - NULL::jsonb values are exported as empty lines
>
> Note: Lines can end with LR or CR+LF, since the CR is just whitespace,
> so even e.g. CR+CR+LF at the end would be allowed.
>

My first impression of this is positive. I like the scoping and the rules
make sense.

I know this is outside of COPY charter as it stands today but I'd like to
suggest allowing for some way of saying "please put input line numbers into
this column". Think of it as adding "with ordinality" to copy. In
particular it would help to deal with potential end-of-file situations
where the last line is imported as a null value.

David J.