Search code examples
postgresqlenums

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

  • 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.