3

Let's say I want to escape these to see a similar string

SELECT *
FROM ( VALUES
  (E'\r\n\t FOO BAR \t\n\r')
) AS t(str);

How do I get the c-style escapes, \r\n\t FOO BAR \t\n\r back out?

SELECT *
FROM ( VALUES
  (E'foo\nfoo',E'\n\tBAR\t','baz'),
  (E'quz','qaz','quz')
) AS t(x,y,z); 
  x  |        y         |  z  
-----+------------------+-----
 foo+|                 +| baz
 foo |         BAR      | 
 quz | qaz              | quz
(2 rows)

Want instead..

  x       |        y         |  z  
----------+------------------+-----
 foo\nfoo | \n\tBAR          | baz
 quz      | qaz              | quz
(2 rows)
0
2

You can use string escape syntax and replace(string,substring,replacement) function like below.

Please note it's heavily dependent on standard_conforming_strings setting. Should be on. You can find details here, here and here.

postgres=# \d t
     Table "public.t"
 Column | Type | Modifiers 
--------+------+-----------
 s      | text | 

postgres=# SELECT s, md5( s ), replace( replace( replace( s, E'\n', '\n' ), E'\t', '\t' ), E'\r', '\r' ) FROM t;
    s     |               md5                |  replace   
----------+----------------------------------+------------
 newline:+| 75c093ed14f1239a3510006326a1a260 | newline:\n
          |                                  | 
 tab:     | 355393c5cab9aa324c6ec90682b13d7e | tab:\t
 cr:\r    | 094911687582e40cfeb6217f8c760543 | cr:\r
(3 rows)
1

postgresql is not doing this. you are looking at the data as represented to you by psql.

but if you want to see bytes, convert the data to bytes, and look at that:

jasen=# set bytea_output to escape;
jasen=# SELECT 
     convert_to(x,'utf8') as x_bytes, 
     convert_to(y,'utf8') as y_bytes, 
     convert_to(z,'utf8') as z_bytes
FROM ( VALUES
  (E'foo\nfoo',E'\n\tBAR\t','baz'),
  (E'quz','qaz','quz')
) AS t(x,y,z); 
  x_bytes   |     y_bytes     | z_bytes 
------------+-----------------+---------
 foo\012foo | \012\011BAR\011 | baz
 quz        | qaz             | quz

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.