Re: New "single" COPY format

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

In response to

Browse pgsql-hackers by date

  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