SQL query to get all values a enum can have

user_125756 0
Viewed 113075

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?

enums postgresql
4 Answers

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'))
user_1646020
Related