On Sat, Dec 21, 2024 at 1:57 AM Joel Jacobson <joel@compiler.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.
