SQL query to get all values a enum can have

Try:

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

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))

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.

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.

Related