ALTER TABLE users ADD CONSTRAINT users_email_check
CHECK ( email ~ '\\A(?i-mx:[\w\.%\+\-]+)@(?i-mx:(?:[a-z0-9\-]+\.)+)(?i-mx:[a-z]{2,})\\Z' );
The above PostgreSQL column constraint gives:
ERROR: invalid regular expression: quantifier operand invalid
Note, the double backslash at Start and End of String anchors were necessary for escape because the statement is placed inside a heredoc.
I figured it out. The regex had two (2) issues:
The ?i-mx: were the by-product of interpolating regex inside regex in Ruby. I simply copied and pasted the resulting interpolation from Ruby. PostgreSQL didn't know how to handle them, and they're unnecessary.
I needed to double-up on all backslashes, like I did for Start and End of String, since the entire statement is placed inside a heredoc.
This is the correct version:
email ~ '\\A[\\w\\.%\\+\\-]+@(?:[a-z0-9\\-]+\\.)+(?:[a-z]{2,})\\Z'
'^[\\w.%+-]+@(?:[a-z0-9-]+\\.)+[a-z]{2,}$'
email ~* '^(?:[\w.%+-]+)@(?:(?:[a-z0-9-]+\.)+)(?:[a-z]{2,})$'