2

I use the following to load data into postgres

\copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV

Usually I select the data from a source db

echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file

It generates a file with tab delimiter fields. It works well for the most part. Up until I try to import multi line text column. The error comes on:

ERROR:  unquoted carriage return found in data

Any recommendations how to overcome this?

2
  • Your export format is broken, or at least doesn't match your import format. You haven't told us anything about the export, so what can we say other than 'fix it'?
    – jjanes
    Nov 22, 2020 at 13:28
  • The way I get it involves posting here a long question, however, I edit it with a simple example.
    – Nir
    Nov 22, 2020 at 14:53

1 Answer 1

1

The mysql command you show seems (experimentally, not from actual knowledge) to generate output which is compatible with PostgreSQL's TEXT format, not the CSV format. Newlines are represented by the two characters \n, tabs by \t, and literal backslashes by \\.

So try to import it in that format, which is the default:

\copy tab FROM /tmp/file
7
  • I think what breaks the import is ^M, can that be?
    – Nir
    Nov 23, 2020 at 8:46
  • More specifically, I believe, something here(part of text): URL variables:\n\t\t\t^M\n<!-- == dump-begin x1nn0o == format: modern !-->^M\n<div id="-app-dump-x1nn0o"
    – Nir
    Nov 23, 2020 at 9:02
  • 1
    Yes, mysql reports the ^M literally, rather than converting into two-character escape sequence \r, so in that character it is not compatible with PostgreSQL's text format. You can pipe the data through sed 's/\r/\\r/g' to fix that.
    – jjanes
    Nov 23, 2020 at 17:55
  • Looking good, what all the characters that can breaks the import? For example I have a suspicion now it fails on ^[
    – Nir
    Nov 24, 2020 at 11:24
  • I think I need to escape all the special ones, ^[ is not the only one
    – Nir
    Nov 24, 2020 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.