Re: New "single" COPY format

From: "Joel Jacobson" <joel(at)compiler(dot)org>
To: "PostgreSQL Hackers" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: New "single" COPY format
Date: 2024-11-08 19:39:45
Message-ID: 826954e1-f800-4871-8a9a-e692c45085cf@app.fastmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Nov 7, 2024, at 17:15, Joel Jacobson wrote:
> Attachments:
> * v18-0001-Introduce-CopyFormat-and-replace-csv_mode-and-binary.patch
> * v18-0002-Add-COPY-format-single.patch
> * v18-0003-Reorganize-option-validations.patch

I want to bring up a potential problem with v18, which has been discussed
before:

On Tue, Oct 15, 2024, at 19:30, Jacob Champion wrote:
> Hi,
>
> Idle thoughts from a design perspective -- feel free to ignore, since
> I'm not the target audience for the feature:
>
> - If the column data stored in Postgres contains newlines, it seems
> like COPY TO won't work "correctly". Is that acceptable?

Example:

CREATE TABLE log (line TEXT);
INSERT INTO log (line) VALUES (E'foo\nbar'), ('baz');
COPY log TO '/tmp/log.txt' (FORMAT 'single');
COPY 2

% cat log.txt
foo
bar
baz

TRUNCATE log;
COPY log FROM '/tmp/log.txt' (FORMAT 'single');
SELECT * FROM log;
line
------
foo
bar
baz
(3 rows)

It would be nice if we could come up with an approach, that didn't introduce
this footgun, while at the same time being convenient for the common use cases.

Ideas?

/Joel

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Daniel Verite 2024-11-08 19:44:30 Re: New "single" COPY format
Previous Message Jim Nasby 2024-11-08 19:34:56 Re: Vacuum statistics