postgresql: "...where X IN <array type column values>" syntax?

Question

I'm having problem with using the values of an array column in a where clause. Complete example to reproduce:

create type public.genre_type as enum ('scifi', 'fantasy', 'crime', 'horror', 'classics');
create table public.reader_profile(
    id integer,
    fave_genres genre_type ARRAY
);
create table public.books(
    id serial not null,
    title text,
    genre_type public.genre_type
);
insert into public.reader_profile(id, fave_genres) values (1, array['crime', 'horror']::public.genre_type[]);
insert into public.reader_profile(id, fave_genres) values (2, array['fantasy', 'scifi']::public.genre_type[]);
insert into public.reader_profile(id, fave_genres) values (3, array['scifi', 'classics']::public.genre_type[]);
insert into public.books(title, genre_type) values ('gone with the wind', 'classics');
insert into public.books(title, genre_type) values ('Foundation', 'scifi');
insert into public.books(title, genre_type) values ('Dune', 'scifi');

-- THE FOLLOWING FAILS!!!
select * from public.books
  where genre_type in (
      select fave_genres from public.reader_profile where id = 2
  );

I've tried ...where genre_type = ANY() per other stackoverflow answers as well as ...where genre_type <@ () and I can't get anything to work! It seems the inner query (which works) is being return as an array type and not a list of values or something. Any help appreciated!!

J Spratt · Accepted Answer · 2019-08-01 19:26:49Z

1

I agree with @Hogan that this seems doable with a JOIN but the syntax you are looking for is the following:

SELECT *
FROM books
WHERE genre_type = ANY(ARRAY(SELECT fave_genres FROM reader_profile WHERE id = 2))
;

Demo

answered Aug 1, 2019 at 19:26

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

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

Comments

Hogan · Accepted Answer · 2019-08-01 19:22:43Z

1

Can I suggest using a join instead?

select * 
from public.books b
join public.reader_profile fg on b.genre_type = ANY(rp.fave_genres) and fg.id = 2

answered Aug 1, 2019 at 19:22

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

1 Comment

yen Over a year ago

oh...! This is nice too! Thanks v much!!! I guess I'll still accept the first answer because it best illustrates what I was doing wrong. I'm gonna investigate both approaches tho. The actual query already has a lot of joins but yeah what's one more, eh? haha

Collectives™ on Stack Overflow

postgresql: "...where X IN <array type column values>" syntax?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related