Dear Postgresql specialists
I would like to seek help with problems I am experiencing with the
COPY command.
We have a user whose 3rd party software exports text data in CSV
format.
The Postgresql import function COPY supports CSV but fails when
importing the data.
I have studied in the Postgresql documentation the specification for
CSV input of the COPY command and found that the data is compliant
with that specification.
In particular, I could confirm that the data uses the same newline
character "\n" for both CSV record termination and new lines within
text.
To eliminate any misunderstandings, I have executed a testcase on
Linux, again exclusively using the Linux "\n" newline character as
follows:
# su postgres
$ psql -d myDB -U postgres
=# CREATE TABLE TEST(FIELD_1 TEXT NOT NULL);
=# INSERT INTO TEST(FIELD_1)VALUES('Hello1 \n\n');
=# INSERT INTO TEST(FIELD_1)VALUES('Hello2');
=# COPY TEST TO '/tmp/TEST.txt' DELIMITER '\t' CSV;
WARNING: CSV fields with embedded linefeed or carriage return
characters might not be able to be reimported
=# DELETE FROM TEST;
DELETE 2
=# COPY TEST FROM '/tmp/TEST.txt' DELIMITER '\t' CSV;
ERROR: unterminated CSV quoted field
CONTEXT: COPY test, line 2: ""
The error messages in the tescase suggest that the implementation does
not agree with the specification in the manual.
I have also verified with other tests that the user's CSV output
conforms with the Postgres specification.
Possibly someone with more in-depth knowledge may have additional
information, e.g. undocumented command parameters, that circumvent
this problem.
The user has only a CSV export function available without any option
to eliminate newline characters from that data.
Unfortunately the user is not in the position to convert the output to
another format or change the software output in any non-standard way.
Any help would be highly appreciated.
Regards
Bernard