Regex replacement in PostgreSQL

I have a table where a particular string field often includes u开发者_C百科nicode for single and double quotes inside of it: \u0027 and \u0022 respectively. So it turns out, I actually need them escaped even more. I need to put an extra \ in front of them.

For example, I need to change \u0027Hello, world\u0027 to \\u0027Hello, world\\u0027

What kind of SQL could perform this kind of an update on the table for all records?

---

If you really need this, then you can use such RE:

UPDATE table SET c = regexp_replace(c, '[^\\]\\(u\d{4})', '\\\\\1', 'g');

Make sure that standard_conforming_strings is enabled and regex_flavor is set to advanced.

SHOW standard_conforming_strings;
 standard_conforming_strings 
-----------------------------
 on
(1 row)

Replacement string '\\\\\1' means two following backslashes \\ and \1 represent first (reporting) parenthesized subexpression (that is, 'u' concatenated with four digits from pattern).

---

An UPDATE statement with SET yourcolumn = REPLACE(yourcolumn, '\u0027', '\\u0027') ought to do it. Try the below first to check that it works before doing a mass update.

SELECT REPLACE('\u0027', '\u0027', '\\u0027')