I have a table which contains value in array something like this.
id | contents_id
1 | [1, 3, 5]
2 | [1, 2]
3 | [3, 4, 6]
4 | [2, 5]
How to write a query array e.g. [1, 2] such that it check value of array not array as a whole ?
If any common value of array is found get all tuples.
If [1, 2] is queried it must fetch id => 1, 2, 4 from above table as it contains 1 or 2.
Consider using the intarray extension. It provides a && operator for testing integer array overlapping. Here is a fiddle, with an example.
select id from test where ARRAY[1,2] && contents_id;
Though you can query it with the operator, I think it will be better to make a junction table with integer IDs.
intarray extension? separately to query this way? happy to see working code in fiddle. thanks for that.
On 1-D int arrays && operator arrayoverlap is the fastest as @LaposhasúAcsa suggested.
so my answer stands only if arrayoverlap is not avaliable or want to work with anything other than one dimensional integer arrays.
Check UNNEST https://www.postgresql.org/docs/current/static/functions-array.html
CREATE TABLE t45407507 (
id SERIAL PRIMARY KEY
,c int[]
);
insert into t45407507 ( c) values
(ARRAY[1,3,5])
, (ARRAY[1,2])
, (ARRAY[3,4,6])
, (ARRAY[2,5]);
select DISTINCT id from
(SELECT id,unnest(c) as c
from t45407507) x
where x.c in (1,2);
Can be shortened with LATERAL join
select DISTINCT id from
t45407507 x,unnest(c) ec
where ec in (1,2);
The comma (,) in the FROM clause is short notation for CROSS JOIN.
LATERAL is assumed automatically for table functions like unnest().
Rewrite WHERE to use ARRAY as parameter
SELECT DISTINCT id FROM
t45407507 x,unnest(c) ec
WHERE ec = ANY(ARRAY[1,2]);
