Postgres replacing 'text' with e'text'

Question

I inserted a bunch of rows with a text field like content='...\n...\n...'.

I didn't use e in front, like conent=e'...\n...\n..., so now \n is not actually displayed as a newline - it's printed as text.

How do I fix this, i.e. how to change every row's content field from '...' to e'...'?

Elias Soares · Answer 1 · 2022-03-09T02:23:57.143

2

Use REPLACE in an update query. Something like this: (I'm on mobile so please ignore any typo or syntax erro)

UPDATE table
SET 
column = REPLACE(column, '\n', e'\n')

edited Mar 09 '22 at 02:23

answered Mar 09 '22 at 01:55

Elias Soares

9,884
4
29
59

Oh, that's interesting! But is \n the only difference between e'...' and '...'? For example, if I had tabs, I'd have to repeat this for e'\t', right? Is there a way to do the whole thing in one go? – Carbs Mar 09 '22 at 02:03
Also, I can't edit it, but in Postgres there should be "FROM" after UPDATE, just UPDATE table... – Carbs Mar 09 '22 at 02:06
I don't think so. You have to replace one by one. – Elias Soares Mar 09 '22 at 02:31
@Carbs: The `FROM` clause is optional in an `UPDATE`, to join in additional relations, and not needed in the example. – Erwin Brandstetter Mar 09 '22 at 02:54

Erwin Brandstetter · Accepted Answer · 2022-03-09T03:08:40.653

The syntax variant E'string' makes Postgres interpret the given string as Posix escape string. \n encoding a newline is only one of many interpreted escape sequences (even if the most common one). See:

Insert text with single quotes in PostgreSQL

To "re-evaluate" your Posix escape string, you could use a simple function with dynamic SQL like this:

CREATE OR REPLACE FUNCTION f_eval_posix_escapes(INOUT _string text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT E''' || _string || ''''  INTO _string;
END
$func$;

WARNING 1: This is inherently unsafe! We have to evaluate input strings dynamically without quoting and escaping, which allows SQL injection. Only use this in a safe environment.

WARNING 2: Don't apply repeatedly. Or it will misinterpret your actual string with genuine \ characters, etc.

WARNING 3: This simple function is imperfect as it cannot cope with nested single quotes properly. If you have some of those, consider instead:

Unescape a string with escaped newlines and carriage returns

Apply:

UPDATE tbl
SET    content = f_eval_posix_escapes(content)
WHERE  content IS DISTINCT FROM f_eval_posix_escapes(content);

db<>fiddle here

Note the added WHERE clause to skip updates that would not change anything. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

Postgres replacing 'text' with e'text'

2 Answers2