Search code examples
postgresqlcopybackuppg-dump

Postgres copy command adding extra backslash "\"

I am trying to copy jsonb type of data into a file.

\copy (select myjsonbcolumn from mytable where time > timestamp '2021-05-01 00:00:00') to '/home/ubuntu/jsobdata.ndjson';

Now this jsonb data have \' within it. e.g.,

{"ID": "123","Body": "<p><a href=\"https://google.com\">Lorem ipsum</a></p>\n<p>Lorem Ipsum Lorem ipsum </p>"}

Now the above copy command adds an extra "" to it, which transforms into below

{"ID": "123","Body": "<p><a href=\\"https://google.com\\">Lorem ipsum</a></p>\\n<p>Lorem Ipsum Lorem ipsum </p>"}

Is there a way to notify not to add extra \? Because this huge data, more than 200GB and to replace those extra \ will take a lot of time via file processing

Solution

  • The problem is that the default output format of COPY, which is text, treats the backslash as an escape character, so it must be doubled. Use the csv format and set quote characters and delimiter to characters that do not occur in your data, for example

    \copy (SELECT ...) TO '...' (FORMAT 'csv', QUOTE E'\u0007', DELIMITER E'\u0008')