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

@dawebber I was trying to avoid COPY since I'm SELECT'ing from a JOIN, and there's no way to use copy on that, right? — Marco
– Marco, Commented Apr 26, 2011 at 16:02
yes you can. Use COPY (SELECT * FROM table) TO ... . It was on the same page I quoted earlier, midway through the page — dawebber
– dawebber, Commented Apr 26, 2011 at 16:26

mu is too short · Accepted Answer · 2011-04-26 20:51:52Z

10

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.

edited Apr 26, 2011 at 20:51

answered Apr 26, 2011 at 2:21

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user330315 Over a year ago

Using the backslash that way is deprecated. It is highly recommended to use standard_conforming_strings = on which will be the default in the next release if I'm not mistaken. postgresql.org/docs/9.0/static/…

mu is too short Over a year ago

@a_horse_with_no_name: What's the best way to get a ` in a string that works in PostgreSQL 8 and 9 without producing any warnings and doesn't care about the standard_conforming_strings` setting? A lot of people (including me) have to deal with both 8 and 9 and many (such as Heroku users) don't have full control over the PostgreSQL configuration.

user330315 Over a year ago

@mu is too short: getting a single quote ' into a string is easy: simply put two quotes into the string: 'Peter''s bike'. That is unaffacted by the standard_conforming_strings. The same is true for a double quote: '12" Inch' standard_conforming_strings is only about using a backslash in the literal

Marco Over a year ago

Thanks guys, that's exactly what I was looking for. By the way would it be ok to do the following to avoid the double backslash: REPLACE(text, '"', E'\x5C"')?

mu is too short Over a year ago

@a_horse_with_no_name: No, I'm talking about the "\" not the quotes (I forgot to close my backticks in the comment and it is too late to fix it); that single backtick is supposed to be `\`. Sigh, looks like I'm having backslash problems in this comment now, how appropriate.

|

dawebber · Accepted Answer · 2011-04-26 16:35:06Z

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.

answered Apr 26, 2011 at 16:35

dawebber

3,5631 gold badge18 silver badges16 bronze badges

Collectives™ on Stack Overflow

Escaping quotes inside text when dumping Postgres Sql

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related