Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux