I did some research on this and it turns out it is one of our TODO
items. It is:
o Allow COPY FROM ... CSV to interpret newlines and carriage
returns in data
This would require major refactoring of the copy source code.
We are actually careful to warn people who dump out data with newlines
in CSV format:
test=> insert into t values (4, 'lkjasdf
test'> lkjasdf
test'>
test'>
test'>
test'> aaaa');
INSERT 542038 1
test=> copy t to '/bjm/3' with null as '' csv quote as '"';
WARNING: CSV fields with embedded linefeed or carriage return characters might not be able to be reimported
COPY
Someone has come up with a patch which might allow this so it might work
in 8.1.
I am attaching a reproducable case of your report.
---------------------------------------------------------------------------
Rick Schumeyer wrote:
> I think I've found a bug in PG 8.0 that occurs while copying from CSV files.
>
> I checked the bugs list but didn't see anything similar.
>
>
>
> This occurs when reading a CSV file where one of the 'text' fields has
>
> a blank line. I included an example that shows the problem below.
>
> I found that if I change the blank line to have one space, pg reads
>
> it just fine.
>
>
>
> If this is indeed a bug, if someone could let me know the best
>
> way to address it, I would appreciate it.
>
>
>
> --EXAMPLE
>
> create table t (
>
> id integer,
>
> description text
>
> );
>
>
>
> copy t from stdin with null as '' csv quote as '"';
>
> 1,"Now is the time"
>
> 2,"for all good men
>
> to come
>
> to the
>
>
>
> aid of their party"
>
> 3,"The quick brown fox"
>
> \.
>
> --END EXAMPLE
>
>
>
> The above example produces this output:
>
>
>
> CREATE TABLE
>
> psql:test2.sql:8: ERROR: unterminated CSV quoted field
>
> CONTEXT: COPY t, line 5: ""
>
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
CREATE TABLE t (
id INTEGER,
description TEXT
);
COPY T FROM stdin WITH NULL AS '' CSV QUOTE AS '"';
1,"Now is the time"
2,"for all good men
to come
to the
aid of their party"
3,"The quick brown fox"
\.