Dev Uberoi Dev Uberoi - 16 days ago 5
SQL Question

How to escape \n (newline) when importing CSV into Postgres using \copy command?

I am trying to import data into my postgres table using \copy from a CSV file
The data is as follows:

1,2,"First Line \n Second Line", "Some other data"


My motive is to preserve '\n' while importing the data.

The following works:

insert into table (col1, col2, col3, col4) values (1, 2, E'First Line \n Second Line', 'Some other data')


But how do I achieve the same result using \copy command?

Answer

The only thing you can escape in CSV is the field delimiter (" by default) which you escape by doubling it.

Line breaks cannot be escaped, and indeed it is not necessary, as you can just use a literal line break.

This is a single line in CSV:

1,2,"First Line
Second Line", "Some other data"
Comments