PostgreSQL POSIX regular expressions raise ParseException

a1ea321 commented 1 month ago

from mo_sql_parsing import parse as p
p("select * from my_table where my_col ~ 'regex'")

This raises a ParseException.

pip show mo-sql-parsing says: Version: 10.652.24214

There's also the ~* variant: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP

These are operators so the problem is actually more generic, since it is possible to define new operators: https://www.postgresql.org/docs/current/sql-createoperator.html

For instance this works on PostgreSQL 16:

CREATE FUNCTION DELETEME(INT, INT) RETURNS BOOLEAN AS $$ SELECT TRUE $$ LANGUAGE SQL;
CREATE OPERATOR =*///+-= (LEFTARG = int, RIGHTARG = int, FUNCTION = deleteme);
SELECT 42 WHERE 1=*///+-=2;

It returns 42 as expected.

I don't care whether your library supports arbitrary operators because I never define any. I suppose most people will be like me. I just wanted to bring it to your attention.