PostgreSQL：无效的正则表达式：无效的反向引用号 [英] Postgresql: Invalid regular expression: invalid backreference number

问题描述

我有这个正则表达式 ^（？！。*？（[aceg]）。*？\1）（？！。*？（[i]）（？：。*？ \2）{2}）[acegi] + $ 作为（例如，在Ruby中），但在PostgreSQL中由于无效的反向引用号而没有出现。

I have this regular expression ^(?!.*?([aceg]).*?\1)(?!.*?([i])(?:.*?\2){2})[acegi]+$ which works as expected (e.g. in Ruby), but not in PostgreSQL due to "invalid backreference number".

如何解决它并保持给定功能？

How to solve it and keep given functionality?

SQL命令的一部分： WHERE（name〜'^（?! 。*？（[aceg]）。*？\1）（？！。*？（[i]）（？：。*？\2）{2}）[acegi] + $'）

Part of the SQL command: WHERE (name ~ '^(?!.*?([aceg]).*?\1)(?!.*?([i])(?:.*?\2){2})[acegi]+$')

注意：我尝试了像 \\ 这样的转义反斜杠，没有任何错误，但是PG返回的是无效匹配项（例如 aaa）。

Note: I tried escaping backslash like \\ without any error, but PG were returning invalid matches (like "aaa").

推荐答案

Postgresql的问题在于，首先，它不支持在其前瞻性断言中包含捕获组。也就是说，在前瞻范围内的所有捕获组都将被视为非捕获组（（?: ...）），重点是我的

The problem with Postgresql is that first, it doesn't support having capture groups within its lookahead assertions. That said, all capture groups within a lookahead will be treated as non-capture groups ((?: ... )), emphasis mine:

先行约束不能包含反向引用（请参见第9.7.3.3节），并且其中的所有括号均被视为未捕获。 ^[1]

Lookahead constraints cannot contain back references (see Section 9.7.3.3), and all parentheses within them are considered non-capturing.^[1]

因此，即使PostgreSQL确实支持在超前时间内进行反向引用，由于上述原因，它仍然无法按预期工作约束（没有捕获组，您不能具有后向引用）。

So even if PostgreSQL did support having backreferences within a lookahead, it would still be failing to work as expected due to the above constraint (without a capture group, you cannot have a backreference).

可能的解决方法（对于复杂的要求将是冗长的不幸的是）将是数每个字符的长度：

A possible workaround (will be lengthy for complex requirements unfortunately) would be to count the number of each character:

WHERE
    LENGTH(REGEXP_REPLACE(name, '[^a]+', '', 'g')) < 2 AND
    LENGTH(REGEXP_REPLACE(name, '[^c]+', '', 'g')) < 2 AND
    LENGTH(REGEXP_REPLACE(name, '[^e]+', '', 'g')) < 2 AND
    LENGTH(REGEXP_REPLACE(name, '[^g]+', '', 'g')) < 2 AND
    LENGTH(REGEXP_REPLACE(name, '[^i]+', '', 'g')) < 3 AND
    LENGTH(REGEXP_REPLACE(name, '[acegi]+', '', 'g')) = 0;

[条件根据这个答案；最后一行是确保字符串中仅包含那些字符]

[condition taken and modified from this answer; the last row is to ensure there are only those characters in the string]

这篇关于PostgreSQL：无效的正则表达式：无效的反向引用号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！