CODEWITHSUNDEEP
sqlarrayspostgresql
JS_Kim
JS_Kim

Reputation: 81

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

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!

Upvotes: 0

Views: 224

Answers (2)

T. Fartek
T. Fartek

Reputation: 141

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

  1. check if any array element contains 1 (this is ok)
  2. 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);

Upvotes: 1

user330315
user330315

Reputation:

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]

Upvotes: 1

Related Questions