1

I'm trying to solve exactly this: How to check if a cell of type integer array contains a certain value in SQL but for multiple values on boths sides

so something like this:

SELECT id FROM table WHERE ALL(column_of_type_integer_array) IN (2,3,4)

Is there anything like this or do I have to split my values up?

Edit:

table callenges

id| precoditions
-----------------
1 |[]
2 |[]
3 |[]
4 |[2,3]

So I only want to select chalenges where the precodition challenges are fullfilled.

SELECT id FROM callenges WHERE All(preconditions) IN ${solvedChallenges}
GMB
  • 216,147
  • 25
  • 84
  • 135
Basti
  • 606
  • 1
  • 12
  • 22

2 Answers2

3

Use the operator <@ - is contained by, e.g.:

with my_table(arr) as (
values
    (array[2,3,4,2,3]),
    (array[1,2,3,4,2,3])
)

select *
from my_table
where arr <@ array[2,3,4]

     arr     
-------------
 {2,3,4,2,3}
(1 row)
klin
  • 112,967
  • 15
  • 204
  • 232
1

I do not see a solution without some kind of splitting, but you do not need to repeat some parts of your query. I would split up the array using unnest():

SELECT m.id
FROM mytable m
WHERE (SELECT bool_and(u.i IN (2, 3, 4)) FROM unnest(m.int_array) u (i))
Islingre
  • 2,030
  • 6
  • 18