SQL query to get all values a enum can have

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?

Solution 1:

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.

Solution 2:

Try:

SELECT e.enumlabel
  FROM pg_enum e
  JOIN pg_type t ON e.enumtypid = t.oid
  WHERE t.typname = 'myenum'

Solution 3:

SELECT unnest(enum_range(NULL::your_enum))::text AS your_column

This will return a single column result set of the contents of the enum "your_enum" with a column named "your_column" of type text.

Solution 4:

You can get all the enum values for an enum using the following query. The query lets you pick which namespace the enum lives in too (which is required if the enum is defined in multiple namespaces; otherwise you can omit that part of the query).

SELECT enumlabel
FROM pg_enum
WHERE enumtypid=(SELECT typelem
                 FROM pg_type
                 WHERE typname='_myenum' AND
                 typnamespace=(SELECT oid
                               FROM pg_namespace
                               WHERE nspname='myschema'))