| From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
|---|---|
| To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Joel Jacobson <joel(at)compiler(dot)org> |
| Cc: | jian he <jian(dot)universality(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: New "single" COPY format |
| Date: | 2025-02-18 14:57:42 |
| Message-ID: | e71386ad-d780-4598-ba85-71be737902f2@dunslane.net |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On 2025-02-17 Mo 7:05 PM, David G. Johnston wrote:
> 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.
>
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Vlada Pogozhelskaya | 2025-02-18 15:07:09 | Re: Improve statistics estimation considering GROUP-BY as a 'uniqueiser' |
| Previous Message | Greg Sabino Mullane | 2025-02-18 14:57:18 | Re: [Feature Request] INSERT FROZEN to Optimize Large Cold Data Imports and Migrations |