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.

• Jeff: What if I made a temp table and did a join on that instead? Would that be faster / more efficient?
• jjanes: I don't think a temp table would be faster. The problem is that a "not like" is not really indexable, and there is nothing much that can change that. Perhaps the most accessible form of optimization would be to put the most-selective string (the one that excludes the most rows) as the first one in the array/list.

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.*';

• Jeff: This doesn't seem to work when I want to exclude things with a dot in them: 'j.*|...|\.'
• Erwin Brandstetter: There are a couple of misconception here. I added another answer.

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)