How to query an array of stored columns in Postgres, (array intersection)
I have a table that contains a value in an array something like this.
id | contents_id
1 | [1, 3, 5]
2 | [1, 2]
3 | [3, 4, 6]
4 | [2, 5]
How to write an array of requests, eg. [1, 2]
to check the value of an array not an array as a whole?
If any common array value is found, get all tuples.
If the query is [1, 2]
requested, it must retrieve the id
=> 1, 2, 4
from the table above as it contains 1
or 2
.
source to share
Consider using the intarray extension. It provides the && operator for checking overlap of an integer array. Here is a violin with an example.
select id from test where ARRAY[1,2] && contents_id;
While you can query it using an operator, I think it would be better to create a join table with integer ids.
source to share
In 1-D int arrays, the &&
operator arrayoverlap
is fastest as suggested by @ LaposhasúAcsa.
so my answer is only worth it if arrayoverlap
not available or wants to work with anything other than one dimensional integer arrays.
Check out 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);
A comma (,) in a sentence FROM
is a short note for CROSS JOIN
.
LATERAL
taken automatically for table functions such as unnest()
.
Rewrite WHERE
to use ARRAY
as parameter
SELECT DISTINCT id FROM
t45407507 x,unnest(c) ec
WHERE ec = ANY(ARRAY[1,2]);
source to share