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];

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.

  • What if I made a temp table and did a join on that instead? Would that be faster / more efficient? – Jeff Nov 18 '15 at 16:32
  • 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. – jjanes Nov 18 '15 at 16:39

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.*';
  • This doesn't seem to work when I want to exclude things with a dot in them: 'j.*|...|\.' – Jeff Nov 17 '15 at 16:27
  • 1
    There are a couple of misconception here. I added another answer. – Erwin Brandstetter Nov 18 '15 at 0:32

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


... 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.