CODEWITHSUNDEEP
sqlarrayspostgresqlenums
Gashio Lee
Gashio Lee

Reputation: 129

How to match an element in an array of enum value?

I have a table books with rows as below:

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

title genres
Deadlock {Horror}
Sunny Hills {Romance, Adventure}
Exiled Prince {Adventure}

I tried:

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

But that only returns the 'Exiled Prince' book.
How do I formulate a query for all rows containing 'Adventure' in its genres array?

Upvotes: 1

Views: 1058

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

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. See:

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

Index

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

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

Why?

Upvotes: 2

Related Questions