PostgreSQL escape character during copy

Question

I'm trying to import a CSV file into PostgreSQL but I am having an issue with special characters.

I'm using the following command

./psql -d data -U postgres -c "copy users from 'users.csv' delimiter E'\t' quote '~' csv"

It works fine until it encounters a field with the '~' which I'm using as a quote value to not break the existing quotes and inverted commas etc.

How do I escape this character in the csv file 'Person~name' so that it will import as 'Person~name'

show simple example how your CSV format looks like and which PostgreSQK version you use. I can't recreate error on my machine. — Gabriel's Messanger
– Gabriel's Messanger, Commented Oct 28, 2015 at 12:34

Daniel Vérité · Accepted Answer · 2015-10-28 16:30:14Z

2

CSV rules are listed in https://www.ietf.org/rfc/rfc4180.txt

To embed the quote character inside a string:

If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example:

"aaa","b""bb","ccc"

In your case, replace double-quote by tilde, since you've choosen that delimiter.

Example:

test=> create table copytest(t text);
CREATE TABLE

test=> \copy copytest from stdin delimiter E'\t' quote '~' csv
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ~foo~~bar~
>> \.

test=> select * from copytest;
   t    
---------
 foo~bar

answered Oct 28, 2015 at 16:30

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL escape character during copy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related