Escaping quotes inside text when dumping Postgres Sql

Question

Let's say my table is:

  id    text
+-----+----------+
  123 | foo bar
  321 | bar "baz"

Is there any way to escape those quotes around 'baz' when dumping?

My query is in the form:

SELECT text FROM aTable WHERE ...

And I would like the output to be:

foo bar
bar \"baz\"

rather than:

foo bar
bar baz

Answer 1

You probably want to use replace:

SELECT REPLACE(text, '"', E'\\"') FROM aTable WHERE ...

You'll need to escape your escape character to get a literal backslash (hence the doubled backslash) and use the "E" prefix on the replacement string to get the right escape syntax.

UPDATE: And thanks to a_horse_with_no_name's usual strictness (a good thing BTW), we have a solution that doesn't need the extra backslash or non-standard "E" prefix:

set standard_conforming_strings = on;
SELECT REPLACE(text, '"', '\"') FROM aTable WHERE ...

The standard_conforming_strings option tells PostgreSQL to use standard syntax for SQL strings:

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard.

This would also impact your \x5C escape:

If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical behavior, where backslash escapes were always recognized.

Answer 2

You can use the following incarnation of the COPY command:

COPY (SELECT * FROM table) TO ... WITH FORMAT 'CSV', ESCAPE '<WHATEVER ESCAPE CHARACTER YOU WANT>'

as described here.

You might not have to do anything, as in some cases your QUOTE option will be doubled automatically. Please consult examples for the referenced link. You can also use VALUES in addition to SELECT. No further data mangling should be necessary.

This is assuming you are using 7.3 or higher. The syntax is slightly different between 7.3 and 9.0, so please consult the appropriate docs.