I have a Postgres table with the array field and with the following values:
I want to pull all the rows which have an id value in the field column and here is the query I tried (now simplified with table alias):
SELECT tb1."id",
tb1."field",
tb1."field1"
FROM "polls_nullableintegerarraymodel" tb1
WHERE tb1."field" IN (ARRAY[tb1."id"])
But for some reason it does not pull the values.
I am expecting something like this:
SELECT am."id", am."field", am."field1"
FROM "polls_nullableintegerarraymodel" am
WHERE am."id" = ANY(am."field");
Also, notice how table aliases make the query much easier to write and to read.
EDIT:
Here is sample code that shows this working:
with t as (
select 1 as id, '{2}'::integer[] as field1 union all
select 2 as id, '{1, 2}':: integer[] as field1
)
select *
from t
where id = any(field1);
This returns only the second row.
EDIT II:
You seem to want this:
SELECT tb1."id", tb1."field", tb1."field1"
FROM "polls_nullableintegerarraymodel" tb1
WHERE tb1."field" <@ (SELECT array_agg("id") FROM tb1);
This is probably a simple way to accomplish what you want, but this appear to be what you are attempting.
id is a number, not an array. The second column is the array.According to your comment:
I want to check if the arrayfield contains any id values
This query returns all rows where the array in field contains any id value from the same table (not just the same row):
SELECT *
FROM polls_nullableintegerarraymodel p1
WHERE EXISTS (
SELECT 1
FROM polls_nullableintegerarraymodel p2
WHERE p2.id = ANY(p1.field)
);
Using the ANY construct In an EXISTS expression:
The same with array operators:
SELECT *
FROM polls_nullableintegerarraymodel p1
WHERE field && (ARRAY(SELECT id FROM polls_nullableintegerarraymodel));
Using the overlaps operator && and a simple ARRAY constructor (faster than array_agg() in simple cases).
But I expect performance of this variant to deteriorate with big tables, since the assembled array grows equally big.
