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)
  • Hm. quote_literal won't do quite what you want because it keeps newlines as literal newlines. I'm a bit dubious about it emitting \r in a non-E'' string with standard_confirming_strings on, too. Same is true of format('%L', ...) – Craig Ringer May 25 '17 at 6:10
  • 1
    Regardless of what the server does, it's a stupid behavior for the client (psql). Does the spec even cover the behavior of the client? It would seem like providing a string function to do this on the server would be handy too. – Evan Carroll May 25 '17 at 6:15
  • 1
    I don't really agree. I prefer to see multiline text as multiline text, personally. You're right that the client is free to display strings as it wants, but users tend to expect those strings to round-trip back into the server with unsurprising results. If you INSERT the string '\r\n' you won't get the result you expect, you have to add E'' notation. So suddenly what psql displays isn't what it accepts as input. Not fun. – Craig Ringer May 25 '17 at 6:16
  • 1
    Maybe I'll give it a shot. =) I do find this to be pretty confusing SELECT * FROM ( VALUES (E'foo\nfoo',E'\n\tBAR\t','baz'),(E'quz','qaz','quz') ) AS t(x,y,z); 2 records, 3 lines. – Evan Carroll May 25 '17 at 6:20
  • 2
    Ugh, rather. That continuation is a bit too subtle. Nicely crafted. I'd probably go "WTF" and look at that with \x – Craig Ringer May 25 '17 at 6:52
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)
0

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.