Redshift / Regular Expression (Negative Lookahead) does not work

Question

When I use negative lookahead (something like (?!abc)) on redshift, redshift returns the error like this:

-- Find records that do not start with abc
select * from table_a where column_a ~ '^(?!abc).+$'

Error: PG::InternalError: ERROR: Invalid preceding regular expression prior to repetition operator. The error occured while parsing the regular expression: '^(?>>>HERE>>>!abc).+$'. DETAIL: ----------------------------------------------- error: Invalid preceding regular expression prior to repetition operator. The error occured while parsing the regular expression: '^(?>>>HERE>>>!abc).+$'. code: 8002 context: T_regexp_init query: 1039510 location: funcs_expr.cpp:130

It looks like Redshift does not recognize negative lookahead...
Is there any way I can use it on Redshift?

Answer 1

Acc. to the Amazon Redshift documentation, the regular expressions you can use with ~ operator comply with the POSIX standard. That means there is no lookaround support and you cannot use (?!...), nor (?<!...) constructs in these patterns.

It seems that you want to match a string if it does not start with a pattern. In this case, you may use the negated regex operator version, !~.

where column_a !~ '^abc'