PostgreSQL csv import from a MySQL csv export?

Question

Will it work?

MySQL export:

SELECT * INTO OUTFILE 'C:/data.csv'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM table;

PostgreSQL:

COPY table FROM 'C:/data.csv' WITH DELIMITER AS '\t' NULL AS '\\N' CSV

There is missing columns for some reason. So I believe there is problem in delimiter. Am I correct, what can I do? I can inspect row with cause error below. But which characters I must look for?

ERROR:  missing data for column "Column21"
CONTEXT:  COPY table, line 88219: ...

So at 88219 line contain sting with escaped quote: " ... \" ... " Seems like postgres fail to unquote this. Is there special option for this? — FirstTimePoster
– FirstTimePoster, Commented Sep 20, 2011 at 12:06

vitalyster · Accepted Answer · 2022-12-16 16:13:16Z

16

As mentioned by @knitti postgres need to know escape character: ESCAPE '\'
OPTIONALLY ENCLOSED BY '"' is bad format for csv. It's better to force quoting.

Full code:

mysql 5.5.15:

SELECT *
INTO OUTFILE 'C:/data.csv'
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM table;

postgres 9.0.3:

COPY table FROM 'C:/data.csv' WITH DELIMITER AS E'\t' NULL AS '\\N' ESCAPE E'\\' CSV

edited Dec 16, 2022 at 16:13

vitalyster

5,2853 gold badges21 silver badges34 bronze badges

answered Sep 21, 2011 at 6:38

FirstTimePoster

2512 silver badges9 bronze badges

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

2 Comments

Henley Wing Chiu Over a year ago

Just what I was looking for my friend!

Zabuzzman Over a year ago

I needed to escape the delimiter as E'\t' for the above to work

knitti · Accepted Answer · 2011-09-20 13:59:59Z

0

Default quote character in PostgreSQL is double-quote, but the documentation says, you can use any single one-byte-character. Try adding

ESCAPE '\\'

edited Sep 20, 2011 at 13:59

answered Sep 20, 2011 at 13:47

knitti

7,03335 silver badges43 bronze badges

Collectives™ on Stack Overflow

PostgreSQL csv import from a MySQL csv export?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related