BUG #7709: psql \copy csv terminates with \. in quoted region
The following bug has been logged on the website:
Bug reference: 7709
Logged by: Timothy Garnett
Email address: tgarnett@panjiva.com
PostgreSQL version: 9.1.2
Operating system: Ubuntu 10.04.4 LTS
Description:
psql \copy terminates at \. by itself in a line even if the format is csv
and the \. is inside a quoted region. This means that some values can't be
round-tripped by psql \copy. Tested and the native postgresql COPY handles
this case correctly.
Ex:
=# CREATE TEMPORARY TABLE test_table AS (SELECT 1 AS id, '
\.
'::text AS data, 2 AS num_data);
=# \copy test_table to '/tmp/dummy.csv' csv
=# TRUNCATE TABLE test_table;
=# \copy test_table from '/tmp/dummy.csv' csv
ERROR: unterminated CSV quoted field
CONTEXT: COPY test_table, line 1: "1,"
"
(postgresql COPY can handle this case though:
=# COPY test_table TO '/tmp/TEST_DATA.csv' WITH (FORMAT CSV);
=# TRUNCATE TABLE test_table;
=# COPY test_table FROM '/tmp/TEST_DATA.csv' WITH (FORMAT CSV);
works fine)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
tgarnett@panjiva.com writes:
psql \copy terminates at \. by itself in a line even if the format is csv
and the \. is inside a quoted region. This means that some values can't be
round-tripped by psql \copy. Tested and the native postgresql COPY handles
this case correctly.
Ugh. This seems like a rather fundamental oversight in the CSV feature.
The problem is that psql has no idea whether the copy is being done in
CSV mode or not --- and even if it did, it doesn't parse the data fully
enough to realize whether a \. line is inside quotes or not.
In the case of out-of-line data files, it might be reasonable to just
dispense with the check for \. altogether and always ship the whole file
to the backend; I think there's a \. check on the backend side. (Not
sure this is safe in V2 protocol, but I doubt anyone cares anymore
about that.)
In the case of in-line data in a script file, CSV mode seems a bit
broken in any case; there's no concept of a terminator in CSV, AFAIK.
So maybe we don't have to worry about that.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Tue, Nov 27, 2012 at 12:33:44PM -0500, Tom Lane wrote:
tgarnett@panjiva.com writes:
psql \copy terminates at \. by itself in a line even if the format is csv
and the \. is inside a quoted region. This means that some values can't be
round-tripped by psql \copy. Tested and the native postgresql COPY handles
this case correctly.Ugh. This seems like a rather fundamental oversight in the CSV feature.
The problem is that psql has no idea whether the copy is being done in
CSV mode or not --- and even if it did, it doesn't parse the data fully
enough to realize whether a \. line is inside quotes or not.In the case of out-of-line data files, it might be reasonable to just
dispense with the check for \. altogether and always ship the whole file
to the backend; I think there's a \. check on the backend side. (Not
sure this is safe in V2 protocol, but I doubt anyone cares anymore
about that.)In the case of in-line data in a script file, CSV mode seems a bit
broken in any case; there's no concept of a terminator in CSV, AFAIK.
So maybe we don't have to worry about that.
I have added a C comment documenting this bug; patch attached.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +