Postgres literal escape sequences

2019-10-09

Postgres automatically escapes all occurrences of escape sequences if strings aren't prefixed with the E escape constant. This can lead to unexpected results:

select array_to_string(array['first line', 'second line', 'third line'], '\n');
           array_to_string
-------------------------------------
 first line\nsecond line\nthird line

Prefixing the separator string with the escape constant tells Postgres to interpret the sequence literally.

select array_to_string(array['first line', 'second line', 'third line'], E'\n');
 array_to_string
-----------------
 first line     +
 second line    +
 third reich

There's no need to prefix the template string when using format, as E-strings are substituted in literally.

select format('first line%ssecond line', E'\n');
   format
-------------
 first line +
 second line

Loading comments...