postgres array field with select in condition

Question

I have a POSTGRES field with 2 fields - integer arrayfield and integer field.

CREATE TABLE test.public.polls (
    "id" serial NOT NULL,
    field_1 _int4,
    field_2 int4 NOT NULL,
    PRIMARY KEY ("id")
);

and the values are

1) Now I need to check if any of the field value {1,2,3} is in the field_1

something like this -

select * from test.public.polls
where field_1 = ANY ('{1,2,3}'::int[])

but this throws an error

operator does not exist: integer[] = integer

2) Need to check if any of the id values = {2,3,4} is in the field_1

select * from test.public.polls
where field_1 = array(id)

not sure what should be the syntax for this.

Could you please clarify the questions? Does first question mean that you want to get records where arrays from field_1 and {1, 2, 3} have any common elements? Does second question mean: get records where field_1 array contains id field? — Alexander Guz
– Alexander Guz, Commented Sep 10, 2016 at 21:01

Kamil Gosciminski · Accepted Answer · 2016-09-10 21:18:48Z

2

Since your field_1 seems to be an array then following should work (this is called overlapping):

select *
from yourtable
where field_1 && '{1,2,3}'::int[]

For the second part it seems like you'd like to aggregate id column and check whether any value from the aggregated set exists within field_1:

select *
from yourtable
where field_1 && (select array_agg(id) from yourtable)

answered Sep 10, 2016 at 21:05

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peterm · Accepted Answer · 2016-09-10 21:04:17Z

2

SELECT * 
  FROM polls
 WHERE '{1,2,3}' && field_1

Here is a SQLFiddle

answered Sep 10, 2016 at 20:56

peterm

93k16 gold badges157 silver badges162 bronze badges