Postgres array lookup multiple values in where clause

Question

I have below table with data

create table stud(key int, person text, subject_id int[]);

insert into stud select 1,'Alex',array[2,7,9];
insert into stud select 2,'Peter',array[4,9,12];
insert into stud select 3,'Tokaver',array[8];
insert into stud select 4,'Machel',array[11,15];

Table looks

I can filter single subject_id in where like

select * from stud where 9=any(subject_id)

How can we filter more than one subject_id in where clause like

select * from stud where (8,9) in any(subject_id)

S-Man · Accepted Answer · 2021-03-04 18:10:10Z

4

demo:db<>fiddle

You can use the overlap operator && for arrays:

select * from stud 
where array[8,9] && subject_id

answered Mar 4, 2021 at 18:10

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Postgres array lookup multiple values in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related