Hello,
I am working on a project where we are converting from MySQL to
Postgres. I figured the easiest way would be to export the MySQL data
as CSV.
I'm having a problem importing some of the data. What I have done is
exported the MySQL data and then modified it so that all single quotes
(a ' quote) are doubled, and null values are replaced with an empty
value in the CSV.
Our data, for example, looks like this:
2628,'Poster,5,'255.255.18.138',,'[quote=gz]I''m curious, why not allow
users, then to freely edit topics?[/quote]
We could either go with "it''s a minor annoyance that is one measure
that helps keep DB load lower," or "we do it just to piss you off."
You choose.',0,1124498148,,,376,0,0,,
This row causes an error. The error is that:
The value "why not allow users" is not valid for column 'x'.
Column x is the first column after the long section of text, with a
value of '0'.
It appears that the quote in "I'm", which has been doubled quoted to
'', is not being properly skipped over, and COPY thinks that the next
comma (after "curious") is a new column, and tries to start inserting
data there. At least that's what I have come up with.
Why is this happening? I've used this method before and I didn't have
any trouble, when loading a bunch of Wikipedia test data (which has all
manner of quotes, commas, and apostrophes in it).
This is line 39150 in the file; all previous lines import fine, but
this kills the COPY process and all of the previous inserts are rolled
back. I need to get this data loaded intact.
My copy command is
COPY posts FROM '/tmp/posts.txt' CSV QUOTE $$'$$;