使用REGEXP_REPLACE時,PostgreSQL返回失敗 - PostgreSQL RETURNING fails with REGEXP_REPLACE -开发者知识库

使用REGEXP_REPLACE時,PostgreSQL返回失敗 - PostgreSQL RETURNING fails with REGEXP_REPLACE -开发者知识库,第1张

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.

我正在運行PostgreSQL 9.4並將大量記錄插入到數據庫中。我使用return子句在插入之后進一步使用。

When I simply run:

當我只是運行:

... RETURNING my_car, brand, color, contact

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

一切都可以,但是如果我嘗試使用REGEXP_REPLACE它會失敗:

... 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.

如果我直接在PostgreSQL中運行查詢,它確實可以工作並返回一個很好的輸出。

2 个解决方案

#1


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:

所以精心設計的@pozs建議理由:

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

正如OP作者所說的,標准_conforming_strings在默認情況下從9.1開始使用psql,在使用pg-prommise時關閉

update from vitaly-t

更新從vitaly-t

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

問題只是JavaScript字面上的轉義,而不是標記。

He elaborates further in his answer

他在回答中作了進一步的闡述

最佳答案:

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复