CODEWITHSUNDEEP
sqldatabasepostgresqlcsvescaping
Dev Uberoi
Dev Uberoi

Reputation: 149

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?

Upvotes: 4

Views: 10972

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51496

I'm afraid you have to modify your csv to backslash \n you want to preserve

b=# copy t from stdout;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> "First Line \n Second Line"
>> "First Line \\n Second Line"
>> \.
COPY 2
b=# select * from t;
              a
-----------------------------
 "First Line                +
  Second Line"
 "First Line \n Second Line"
(2 rows)

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246568

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"

Upvotes: 8

Related Questions