2013/4/5 Konstantin Izmailov <pgfizm@gmail.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