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

4 Answers 4


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
    Commented Nov 18, 2015 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
    Commented Nov 18, 2015 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
    Commented Nov 17, 2015 at 16:27
  • 1
    There are a couple of misconception here. I added another answer. Commented Nov 18, 2015 at 0:32

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.

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 and acknowledge you have read our privacy policy.

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