PostgreSQL RETURNING与REGEXP_REPLACE一起使用失败

Question

我正在运行PostgreSQL 9.4，并向我的数据库插入大量记录。我在插入后使用RETURNING子句进行进一步的使用。

当我简单地运行时：

... RETURNING my_car, brand, color, contact

一切都正常，但如果我尝试使用REGEXP_REPLACE，它会失败：

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

它失败了，显示如下：

ERROR: invalid regular expression: quantifier operand invalid

如果我直接在PostgreSQL中运行查询，它确实可以工作并返回一个不错的输出结果。

- Michael Nielsen

---

请确保您不会意外关闭standard_conforming_strings（即select current_setting('standard_conforming_strings')）。-- 从9.1开始，默认情况下启用它。如果它是off，那么语法将是'^(\\+?|00)'。此外，有一种特殊的语法，无论这个选项如何都能工作：E'^(\\+?|00)'。但通常最好保持打开状态以避免出现意外情况。 - pozs

开始了。手动运行regexp_replace查询也可以，但将其添加到RETURNING中会导致插入失败。 - Michael Nielsen

请问您能否填充contact的值，以便我们确定它在哪里失败了吗？ - Vao Tsun

@VaoTsun 它可以每个会话进行设置。 - pozs

@VaoTsun 在所有情况下都失败，例如'[phone]'。但手动运行查询可以正常工作。同样，在RETURNING子句中才会失败。 - Michael Nielsen

@MichaelNielsen - 请更新原帖并附上错误示例，类似于我“回答”中的示例。 - Vao Tsun

Answer 1

尝试复制但失败了：

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

INSERT 0 1

如下是@pozs提供的详细解释：

所以 @pozs 提出的原因如下：

set standard_conforming_strings to off;

导致

WARNING:  nonstandard use of escape in a string literal
LINE 1: ...alues ('[phone]') 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

更新 根据原始帖子的说法，standard_conforming_strings 在 9.1 版本中默认为 on 与使用 psql 工作，并且在使用 pg-promise 时为 off

vitaly-t 的更新

问题只是出在 JavaScript 字面量转义上，而不是标志本身。

他在他的回答中进一步阐述了这一点。

- Vao Tsun

---

是的...看起来像是标准一致字符串的问题，就像你所描述的那样。只是不知道如何告诉nodejs打开它，因为在使用psql时通常是打开的。 - Michael Nielsen

我是 pg-promise 库的作者。该库不会对标签进行任何操作，底层驱动程序 node-postgres 也是如此。这只是服务器上默认数据库会话的设置方式。 - vitaly-t

好的，我明白了。当我通过pgsql登录时，“standard_conforming_strings”是开启的。但是通过pg-promise运行查询需要在字符串上使用\转义。 - Michael Nielsen

@MichaelNielsen 在这个上下文中，该变量实际上没有任何意义。请看我的回答。 - vitaly-t

@VaoTsun 这个不正确：and is off working with pg-promise。 它是 true，与 psql 相同。问题仅仅出在 JavaScript 字面量转义上，而不是标志。请查看我的答案。 - vitaly-t

Answer 2

环境变量standard_conforming_strings的当前值在这里是无关紧要的。如果您在查询前缀中添加SET standard_conforming_strings = true;，则可以看到它，但不会改变任何内容。

从客户端传递未经转义的正则表达式字符串与使用命令行的E前缀相同：E'^(\+?|00)'。

在JavaScript中，\被视为特殊符号，您始终必须提供\\来指示该符号，这是正则表达式所需的。

除此之外，pg-promise将正确地转义所有内容，以下是一个示例：

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

要了解命令行的工作原理，请在正则表达式字符串前加上E：

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

您将收到相同的错误信息：无效的正则表达式：量词操作数无效。

- vitaly-t