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?

arthur
  • 888
  • 4
  • 13
  • 29

2 Answers2

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.

hbn
  • 585
  • 2
  • 8
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 ',' "
Guasqueño
  • 109
  • 1