0

Let's say I have a BOOKS table with the records below below:

The genres field is an array of enum that containes the genre of the book and these are its values: ['adventure', 'horror', 'romance']

Record 1

title: 'Deadlock'
genres: ['Horror']

Record 2

title: 'Sunny Hills'
genres: ['Romance', 'Adventure']

Record3

title: 'Exiled Prince'
genres: ['Adventure']

I did something like this:

SELECT * FROM books WHERE genres = ARRAY['Adventure']::book_genres_enum[]

With the query above, I'm only able to get the 'Exiled Prince' book. With the records above, how do I formulate a query that can get all the records that has Adventure in its genre?

Gashio Lee
  • 119
  • 1
  • 15

1 Answers1

1

The simple query is with = ANY:

SELECT * FROM books
WHERE  'Adventure'::book_genres_enum = ANY(genres)

This works with any type of array. Nothing special about an enum type in this regard.

The explicit cast is optional. When passing 'Adventure' as untyped string literal, the data type is derived from context. (Does not work with typed values.)

Index

If the table is big and you have a GIN index on books(genres), use array operators instead - the query is equivalent:

SELECT * FROM books
WHERE  genres @> '{Adventure}'::book_genres_enum[];

Why?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228