Is there an easy way in PostgreSQL to escape newlines, carriage returns, and tabs?

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)

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)

StackExchange Database Administrators Q#174513, answer score: 2