Re: bug in COPY implementation (all versions of Postgres)?

From: Ian Lawrence Barwick <barwick(at)gmail(dot)com>
To: Konstantin Izmailov <pgfizm(at)gmail(dot)com>
Cc: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: bug in COPY implementation (all versions of Postgres)?
Date: 2013-04-05 05:35:21
Message-ID: CAB8KJ=i1Fpap+jdEd=zo0zg49YHtsD5jtgaHzo9vYOgTVU87Eg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

2013/4/5 Konstantin Izmailov <pgfizm(at)gmail(dot)com>:
> I came across an issue that looks like a bug in COPY. There are many similar
> posts, e.g.
> http://stackoverflow.com/questions/13485030/strange-postgresql-value-too-long-for-type-character-varying500,
> without a good unswer.
>
> Simplified steps to reproduce the issue:
> 1. CREATE TABLE TEST (description varchar(10));
> 2. Insert value 'Galaxy\040Tab' using command COPY TEST(description) FROM
> stdin WITH DELIMITER '|' CSV.
>
> The following error is returned: value too long for type character
> varying(10)
>
>
> Of course real life scenarios are more complex and different characters were
> used (\042 and \005).
>
> Is this a bug, or an incorrect use of COPY/CSV?

The latter.

testdb=> COPY vtest(descr) FROM STDIN WITH (DELIMITER '|', FORMAT CSV);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Galaxy\040Tab
>> \.
ERROR: value too long for type character varying(10)
CONTEXT: COPY vtest, line 1, column descr: "Galaxy\040Tab"
testdb=> COPY vtest(descr) FROM STDIN WITH (DELIMITER '|', FORMAT TEXT);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> abcdef\040xyz
>> \.

testdb=> SELECT * from vtest;
descr
------------
abcdef xyz
(1 row)

From the documentation:

"CSV Format

This format option is used for importing and exporting the Comma
Separated Value (CSV) file format used by many other programs, such as
spreadsheets. Instead of the escaping rules used by PostgreSQL's
standard text format, it produces and recognizes the common CSV
escaping mechanism."

http://www.postgresql.org/docs/current/static/sql-copy.html#AEN66712

i.e. the normal escaping rules don't apply with CSV format.

Regards

Ian Barwick

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Szymon Guz 2013-04-05 07:12:13 Re: Oracle to PostgreSQL transition?
Previous Message Konstantin Izmailov 2013-04-05 04:35:16 bug in COPY implementation (all versions of Postgres)?