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?
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"
COPY ... FROM PROGRAM 'command' and substitute a command that massages the CSV file for you.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)
