Postgres POSIX正则表达式问题:括号不平衡

Using the ~* operator on postgres, I get the following error :

无效的正则表达式:方括号[]不平衡

    SELECT     ( 
               CASE table1.type 
                          WHEN 'id' THEN table1.id 
                          WHEN 'keyword' THEN table1.keyword 
               END) AS initial_type, 
               "table2"."id", 
               "table1"."id",
               "error_msg" 
    FROM       "table3" 
    INNER JOIN "table2" 
    ON         "table2"."id" = "table3"."foreign_id" 
    WHERE      ( 
                          "table2"."text_to_match" ~* 'foo[]' 
               OR         "table2"."text_to_match_2" ~* 'bar[]'
) 

AND        "status" = 1 
AND        "has_error" IN (0, 
                           2) 
AND        "table1"."user_id" = 23 
ORDER BY   "table1"."id" ASC limit 1

What I tried is encasing the string to match with the escape string constant E'foo[]' and using dollar signs to escape $$foo[]$$ and none have really worked. That said, the brackets on my strings are matched, so I'm not sure why its throwing Not balanced exception. I'm not sure if this is a posix regex/postgres issue.

如果您需要更多详细信息,请告诉我。任何帮助将非常感激。

评论
  • nerror
    nerror 回复

    If you want to match against literal square brackets, then you need to escape them, as they are otherwise meanigful to the regexp engine (in POSIX regular expressions, [] define character classes):

    ~* 'foo\[\]'