Where column not like multiple values

Question

I am trying to select records in a postgresql db where username is not like a list of strings.

SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%';

The issue is there are a lot of these values. Is there a way to create an array of those and say something like:

SELECT * FROM rails_db WHERE username NOT LIKE ARRAY[my values];

Answer 1

You almost had the correct syntax. This is exactly what you want:

SELECT * FROM rails_db WHERE username NOT LIKE ALL(ARRAY[my values]);

This is a very nice syntax, but it won't necessarily be fast, especially not if the array is large. However, what you want is hard to optimize for performance, and there is no reason to think uglier syntaxes will be any faster.

Answer 2

I may have found it, but I want to see if this works the way it is supposed to:

SELECT * FROM rails_db WHERE username !~* 'j.*|.*eepy.*';

Answer 3

Your idea of using a regular expression with branches is solid. But in your answer you got the translation of % in LIKE patterns wrong. This:

... WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%';

translates to:

... WHERE username !~ '^j|eepy';

!~ is case sensitive like NOT LIKE.
Use !~* to match case insensitive like NOT ILIKE.
To also exclude strings containing a dot (.) anywhere and with another example that matches (doesn't match) the end of the string like username NOT LIKE '%end':

... WHERE username !~ '^j|end$|eepy|\.';

Probably not very fast either.

Answer 4

with help(term) as (values('T%'),('STAG%'))
select tabschema, tabname
from syscat.indexes
where not exists (select 1 from help where tabschema like term)