5

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?

2 Answers 2

6

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.

1
  • 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.
    – Guasqueño
    Jan 5, 2023 at 14:06
1

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 ',' "

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.