3

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.

Michael Nielsen
  • 1,194
  • 3
  • 22
  • 37
  • 1
    Please make sure you don't accidentally turn off [`standard_conforming_strings`](https://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS) (i.e. `select current_setting('standard_conforming_strings')`). -- It is enabled by default on and after 9.1. **If** it's `off`, than the syntax would be `'^(\\+?|00)'`. Also, there is a special syntax, which works regardless of this option: `E'^(\\+?|00)'`. But it is usually less surprising to leave it `on`. – pozs Jun 06 '17 at 11:46
  • It's on. Running the regexp_replace query manually also works, but adding it to RETURNING makes the insert fail. – Michael Nielsen Jun 06 '17 at 11:48
  • can you please populate value of `contact` on which it fails?.. – Vao Tsun Jun 06 '17 at 11:49
  • @VaoTsun it can be set per session – pozs Jun 06 '17 at 11:49
  • @VaoTsun it fails on all cases, ex. '+4422848566'. But running the query manually works. Again, it's only when in the RETURNING clause it fails. – Michael Nielsen Jun 06 '17 at 11:53
  • @MichaelNielsen - please update original post with example of error, similar to the one in my "answer" – Vao Tsun Jun 06 '17 at 11:58

2 Answers2

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

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • I'm using pg-promise from nodejs to initiate the session and use the database. – Michael Nielsen Jun 06 '17 at 12:19
  • Yes... Seems like it is a standard_conforming_strings issue, correctly as you described. Just don't know how to tell nodejs to have it on, as it usually is on when using psql. – Michael Nielsen Jun 06 '17 at 12:30
  • Contact the author of [pg-promise](https://github.com/vitaly-t/pg-promise) or change the title to something like *Confusion with default value of standard_conforming_strings in pg-promise* and add tag `pg-promise` to the question (he looks here from time to time). – klin Jun 06 '17 at 12:48
  • If I'm not mistaken - `pg-promise` just uses briancs `pg`, which btw does not change default value of `standard_conforming_strings` – Vao Tsun Jun 06 '17 at 12:49
  • @MichaelNielsen you can try adding `console.log("_send(code, more)", code, more);` to `connection.js` as described at https://github.com/brianc/node-postgres/issues/314 - this should show you current `standard_conforming_strings` config value – Vao Tsun Jun 06 '17 at 12:51
  • @MichaelNielsen : Maybe you should add pg-promise to the tags for the question. – joop Jun 06 '17 at 14:17
  • I'm the author `pg-promise`. The library doesn't do anything with the tags, neither does the underlying driver `node-postgres`. This is simply how the default database session is set up on the server. – vitaly-t Jun 07 '17 at 07:09
  • 1
    Ok, I see. When I log in via pgsql "standard_conforming_strings" is on. But running queries via pg-promise requires \\ escape on strings. – Michael Nielsen Jun 07 '17 at 07:20
  • @MichaelNielsen The variable is actually of no meaning in this context. See my answer. – vitaly-t Jun 07 '17 at 08:20
  • @VaoTsun This is incorrect: `and is off working with pg-promise`. It is `true`, the same as with psql. The issue is simply with the JavaScript literal escaping, not with the flag. See my answer. – vitaly-t Jun 07 '17 at 08:49
1

The current value of environment variable standard_conforming_strings is inconsequential here. You can see it if you prefix your query with SET standard_conforming_strings = true;, which will change nothing.

Passing in a regEx string unescaped from the client is the same as using E prefix from the command line: E'^(\+?|00)'.

In JavaScript \ is treated as a special symbol, and you simply always have to provide \\ to indicate the symbol, which is what needed for your regular expressions.

Other than that, pg-promise will escape everything correctly, here's an example:

db.any("INSERT INTO users(name) VALUES('hello') RETURNING REGEXP_REPLACE(name, $1, $2)", ['^(\\+?|00)', 'replaced'])

To understand how the command-line works, prefix the regex string with E:

db.any("INSERT INTO users(name) VALUES('hello') RETURNING REGEXP_REPLACE(name, E$1, $2)", ['^(\\+?|00)', 'replaced'])

And you will get the same error: invalid regular expression: quantifier operand invalid.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138