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

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-12-28 05:37:18Z

2

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:

Check if value exists in Postgres array

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?

Can PostgreSQL index array columns?

edited Dec 28, 2023 at 5:37

answered Jul 16, 2022 at 0:20

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Gashio Lee Over a year ago

Thank you! I didn't know regular array operators worked with enum types.

Erwin Brandstetter Over a year ago

@GashioLee: They do because they work with any type.

Collectives™ on Stack Overflow

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

1 Answer 1

Index

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Index

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related