Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (id int, content text);
INSERT INTO tbl VALUES
  (1, 'plain')               -- all good
, (2, 'line1\nline2')        -- newline
, (3, 'line1\nline2\nline3') -- more newlines
, (4, 'tab\ttab')            -- tab
;
4 rows affected

TABLE tbl;


id
content


1
plain

2
line1\nline2

3
line1\nline2\nline3

4
tab\ttab


CREATE OR REPLACE FUNCTION f_eval_posix_escapes(INOUT _string text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT E''' || _string || ''''  INTO _string;
END
$func$;
-- I added a RETRUNING clause to show that only rows needing a change are updated:
UPDATE tbl
SET    content = f_eval_posix_escapes(content)
WHERE  content IS DISTINCT FROM f_eval_posix_escapes(content)
RETURNING *;


id
content


2
line1
line2

3
line1
line2
line3

4
tab tab


TABLE tbl;


id
content


1
plain

2
line1
line2

3
line1
line2
line3

4
tab tab