Geek Answers Handbook

Exception of quotes inside text when dumping Postgres Sql

Say my table:

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

Is there a way to avoid these quotes around "baz" when discarding?

My request is in the form:

SELECT text FROM aTable WHERE ...

And I would like the result to be:

foo bar
bar \"baz\"

but not:

foo bar
bar baz
+5
source share
3 answers

You might want to use replace:

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

You will need to escape the escape character to get a literal backslash (hence double the backslash) and use the prefix "E" on the replacement string to get the correct escape .

. a_horse_with_no_name ( BTW) , "E":

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

standard_conforming_strings PostgreSQL SQL:

, ('...') , SQL.

\x5C escape:

standard_conforming_strings , PostgreSQL , escape-. , .

+4

COPY:

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

.

, , QUOTE . , . VALUES SELECT. .

, 7.3 . 7.3 9.0, .

+1

, , CygWin:-), , - :

blah blah blah | sed 's/"/\\"/g'

, :

pax$ echo '
  id    text
+-----+----------+
  123 | foo bar
  321 | bar "baz"' | sed 's/"/\\"/g'

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

More articles:


All Articles