postgresql - import MySQL dump into Postgres and escape \

I received a MYSQL dump and needs to push it into the Postgres table.

Inside this poi_dump.sql dump, there are many rows of insert. The example as below:

Insert into table values (1,'hello','\rthis is a beautiful Alan\'s cottage', '\nbyebye')
Insert into table values (1,'hello','\rthis is a big\"Work', '\nbyebye')

I am using PSQL command:

psql analytics < poi_dump.sql

However, the postgres does not recognize all the \ used to escape in a ' ' field.

Errors From Postgres:
Query buffer reset (cleared).
invalid command \nOn
invalid command \n
invalid command \'
invalid command \'
invalid command \'
invalid command \n
invalid command \"Without
invalid command \nPort-a-loo
Query buffer reset (cleared).
invalid command \n

How can i make postgres to accept those \r ,\n , 's inside a quote field?

1 Answer

  1. Donald- Reply

    2019-11-16

    PostgreSQL doesn't recognize backslash in string by default. But this behave can be simply changed:

    ides_jmmaj_prac=# select 'hello\nworld';
    ┌──────────────┐
    │   ?column?   │
    ╞══════════════╡
    │ hello\nworld │
    └──────────────┘
    (1 row)
    
    Time: 0,496 ms
    ides_jmmaj_prac=# set standard_conforming_strings TO off;
    SET
    Time: 0,251 ms
    ides_jmmaj_prac=# select 'hello\nworld';
    WARNING:  nonstandard use of escape in a string literal
    ŘÁDKA 1: select 'hello\nworld';
                    ^
    DOPORUČENÍ:  Use the escape string syntax for escapes, e.g., E'\r\n'.
    ┌──────────┐
    │ ?column? │
    ╞══════════╡
    │ hello   ↵│
    │ world    │
    └──────────┘
    (1 row)
    
    Time: 0,496 ms
    

    Disable warnings by:

    set escape_string_warning to off;
    

    Probably \r should be removed. PostgreSQL using Unix end of lines - only \n (I didn't test it).

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>