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'...'?

CodePudding user response：

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')

CodePudding user response：

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?