PostgreSQL RETURNING fails with REGEXP_REPLACE

Question

I'm running PostgreSQL 9.4 and are inserting a lot of records into my database. I use the RETURNING clause for further use after an insert.

When I simply run:

... RETURNING my_car, brand, color, contact

everything works, but if I try to use REGEXP_REPLACE it fails:

... RETURNing my_car, brand, color, REGEXP_REPLACE(contact, '^(\+?|00)', '') AS contact

it fails with:

ERROR: invalid regular expression: quantifier operand invalid

If I simply run the query directly in PostgreSQL it does work and return a nice output.

Answer 1

Tried to reproduce and failed:

t=# create table s1(t text);
CREATE TABLE
t=# insert into s1 values ('+4422848566') returning REGEXP_REPLACE(t, '^(\+?|00)', '');
 regexp_replace
----------------
 4422848566
(1 row)

INSERT 0 1

So elaborated @pozs suggested reason:

set standard_conforming_strings to off;

leads to

WARNING:  nonstandard use of escape in a string literal
LINE 1: ...alues ('+4422848566') returning REGEXP_REPLACE(t, '^(\+?|00)...
                                                             ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR:  invalid regular expression: quantifier operand invalid

update As OP author says standard_conforming_strings is on as supposed from 9.1 by default working with psql and is off working with pg-prommise

update from vitaly-t

The issue is simply with the JavaScript literal escaping, not with the flag.

He elaborates further in his answer