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

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


    SELECT     ( 
               CASE table1.type 
                          WHEN 'id' THEN 
                          WHEN 'keyword' THEN table1.keyword 
               END) AS initial_type, 
    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, 
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\[\]'