Is it possible to turn off quote processing in the Postgres COPY command with CSV format?

user_22263 0
Viewed 18834

I have CSV files, tab-separated, fields not wrapped in quotes, where field data can contain characters like single quotes, double quotes, pipes and backslashes.

Sample data can look like this:

1       2       "ba$aR\eR\       18

I want to import this data into Postgres using the COPY statement.

When I try to import this using 

COPY <tablename> FROM  <filename> NULL AS '';

I get an error psql:-:1: ERROR: missing data for column because Postgres is treating the backslash + tab as an "escaped tab" instead of a backslash followed by the field separator.

So I switched to using the "CSV format" of the COPY operator, like so:

COPY <tablename> FROM <filename> WITH CSV DELIMITER E'\t' NULL AS '';

Now there's a new error psql:-:1: ERROR: value too long for type character varying(254)

Apparently because it's interpreting the double-quote at the start of field 3 as the field wrapping character.

How can I specify that my data is NOT quoted at all?

import csv postgresql
3 Answers

Workaround (thanks to this comment!)

COPY <tablename> FROM <filename> WITH CSV DELIMITER E'\t' QUOTE E'\b' NULL AS '';

So basically specifying a quote character that should never be in the text, but that's pretty ugly.

I'd much prefer it if there was in fact a way to turn off quote processing altogether.

user_222630

(Added as a new answer since I don't have the reputation yet to comment.)

For the record, since I've been struggling with the same issue, you can use tr to remove \b, instead of just hoping it's not in your text anywhere. 

tr -d '\010' < filename.csv > newfile.csv

(Using that \010 is the octal representation of \b).

Since COPY supports reading from STDIN, you can ease the I/O impact by piping tr's output:

cat filename.csv | tr -d '\010' | COPY <tablename> FROM STDIN WITH CSV DELIMITER E'\t' QUOTE E'\b' NULL AS '';
user_73192500
Related