Is it possible to turn off quote processing in the Postgres COPY command with CSV format?
csv, import, postgresql
Solution
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.
Problem
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?