CODEWITHSUNDEEP
sqlarrayspostgresql
user1050619
user1050619

Reputation: 20856

Selecting array values with IN clause

I have a Postgres table with the array field and with the following values:

enter image description here

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.

Upvotes: 0

Views: 227

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 2

Related Questions