Finding values in postgres array (some must be in, some must not be in at the same query)

Question

I have a ids integer[]

And, I want to find rows which contain 1 but must not contains 2, 3, 4

but [1] OR [1, 5] OR [1, 6, 7] <- this data is OK. [2,3,4] is not.

So I tried this way

SELECT * 
FROM table_test 
WHERE 1 = ANY(ids) AND 2 <> ANY(ids)  AND 3 <> ANY(ids)  AND 4 <> ANY(ids)

but it returns 1 = ANY(ids) part

[1 2 3]
[1 3 4]
[1]
[1 5]
[1 6 7]

I want this data

[1]
[1 5]
[1 6 7]

How can I solve this problem?

Thanks a lot!

user330315 · Accepted Answer · 2020-06-02 06:56:20Z

1

You should use ALL together with <>.

The expression 2 <> ANY(ids) is true if at least one element is not equal to 2 - which is always the case because you require at least one element to be 1 (which is not 2) with the first condition.

SELECT * 
FROM table_test 
WHERE 1 = ANY(ids) 
  AND 2 <> ALL(ids)  
  AND 3 <> ALL(ids)  
  AND 4 <> ALL(ids)

another option is to use the overlaps operator && ("have elements in common") and negate it:

SELECT * 
FROM table_test 
WHERE 1 = ANY(ids) 
  AND NOT ids && array[3,4,5]

answered Jun 2, 2020 at 6:56

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

JS_Kim Over a year ago

Thanks! it worked. You gave me 2 choices. Is there any performance difference? Can you tell me which way is more efficient? In my case, the number which must be in is always one. but the numbers mst not be in are not fixed. it could be one or two or more.

user330315 Over a year ago

Check the execution plan but I doubt there is a performance difference.

T. Fartek · Accepted Answer · 2020-06-02 07:09:13Z

1

Your query is very close, but what is actually does is:

check if any array element contains 1 (this is ok)
check if any array element does not contain 2, 3 and 4 (this means [1,3,4] is valid beacuse 1 is not 2,3 or 4, so the condition is fulfilled)

What you really have to check with case #2 is that ALL elements are not 2, 3, 4.

Your updated query is now:

SELECT * FROM table_test WHERE 1 = ANY(ids) AND 2 <> ALL(ids)  AND 3 <> ALL(ids)  AND 4 <> ALL(ids);

answered Jun 2, 2020 at 7:09

T. Fartek

1412 bronze badges

1 Comment

JS_Kim Over a year ago

Thanks for your kindness. your explanation is very useful!

Collectives™ on Stack Overflow

Finding values in postgres array (some must be in, some must not be in at the same query)

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related