Escaping delimiter in postgresql

Question

I am trying to import data into postgres. Data fields are comma separated, strings may contain commas, in which case those are escaped:

Data structure:

create table v (a varchar);

Data file:

bus
'Gat,円\\e\'way_MQB'

Import command (run under Linux from bash)

cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\\' CSV header"

Error:

ERROR: extra data after last expected column

I've been fighting and google against this problem for quite a few hours and do not understand what I am doing wrong?

Answer 1

Try:

cat data.csv | psql -d database -c "copy v (a) from stdin with delimiter ',' escape '\' quote '''' CSV header"

You don't need to escape the escape character when specifying it. QUOTE defaults to double-quote so you need to pass that.

• Guasqueño: It doesn't work for me. I don't have problem with single or doble quotes. Data already comes with some escaped commas this way ,円 so the COPY is not to take it as a separator. I am using ESCAPE '\' in the command line but it doesn't work.

Answer 2

I my case I made it work by removing the CSV parameter and consequently the ESCAPE '\' parameter, since the file was not really a CSV file with doble quotes. The escaping was done without me specifying any escaping parameter. The default is probably the backslash. So, my final command is like this:

psql -d mydatabase -c "\COPY myDBtable FROM 'myfile.csv' DELIMITER ',' "