Selecting array values with IN clause

Question

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.

There are various ways to read your question. Please clarify. Provide data as *text* not as screenshot. Provide your Postgres version and table definition, make the example meaningful and add the expected result. — Erwin Brandstetter, Sep 19 '16 at 13:49

Gordon Linoff · Answer 1 · 2016-09-19T02:08:01.333

2

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.

edited Sep 19 '16 at 02:08

answered Sep 19 '16 at 01:06

Gordon Linoff

1,242,037
58
646
786

My requirement is slightly different. I want to check if the arrayfield contains any id values. Updated with the alias. – user1050619 Sep 19 '16 at 01:33
That makes no sense. `id` is a number, not an array. The second column is the array. – Gordon Linoff Sep 19 '16 at 01:37
I want to convert all the ids into a array and evaluate against the field1 column. – user1050619 Sep 19 '16 at 02:02

Erwin Brandstetter · Answer 2 · 2023-05-31T12:07:50.637

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:

How to use ANY instead of IN in a WHERE clause?

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.

Selecting array values with IN clause

2 Answers2