SQL query to get all values a enum can have

postgresql enums
Wienczny picture Wienczny · Oct 23, 2009 · Viewed 78k times · Source

Postgresql got enum support some time ago. 

CREATE TYPE myenum AS ENUM (
'value1',
'value2',
);

How do I get all values specified in the enum with a query?

Answer

Chris L picture Chris L · Jul 25, 2013

If you want an array:

SELECT enum_range(NULL::myenum)

If you want a separate record for each item in the enum:

SELECT unnest(enum_range(NULL::myenum))

Additional Information

This solution works as expected even if your enum is not in the default schema. For example, replace myenum with myschema.myenum.

The data type of the returned records in the above query will be myenum. Depending on what you are doing, you may need to cast to text. e.g.

SELECT unnest(enum_range(NULL::myenum))::text

If you want to specify the column name, you can append AS my_col_name.

Credit to Justin Ohms for pointing out some additional tips, which I incorporated into my answer.

Related questions

Adding a new value to an existing ENUM Type

I have a table column that uses an enum type. I wish to update that enum type to have an additional possible value. I don't want to delete any existing values, just add the new value. What is the simplest …

Read More
How to delete an enum type value in postgres?

How do I delete an enum type value that I created in postgresql? create type admin_level1 as enum('classifier', 'moderator', 'god'); E.g. I want to remove moderator from the list. I can't seem to find anything on the …

Read More
Hibernate mapping between PostgreSQL enum and Java enum

Background Spring 3.x, JPA 2.0, Hibernate 4.x, Postgresql 9.x. Working on a Hibernate mapped class with an enum property that I want to map to a Postgresql enum. Problem Querying with a where clause on the enum column throws an exception. …

Read More