On 2025-02-17 Mo 7:05 PM, David G. Johnston 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.
I'd tweak this a bit:
. call the format jsonl
. allow json - on output I would just eat any LF, but I could per persuaded to do something else.
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.
Let's not add feature creep. If you want a feature like this there's no reason it should belong only to JSON input.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
