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?
-
I have also found the following answer very helpful: stackoverflow.com/questions/9540681/list-postgres-enum-typeanonymous coward– anonymous coward2013-09-18 15:21:17 +00:00Commented Sep 18, 2013 at 15:21
Add a comment
|
5 Answers
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.
9 Comments
Darin Peterson
This answer is much more concise. Nice contribution!
Justin Ohms
The unnest call will return records of type myenum with a column name of "myenum". You might also want to cast the enum to text and specify a column name by adding something like. ::text AS my_column
Bikal Basnet
To understand more about enum functions you can view this link postgresql.org/docs/8.3/static/functions-enum.html postgresql.org/docs/9.2/static/functions-array.html
mc9
what is the meaning of
NULL::?mc9
@ChrisL thanks. it seems very strange. why can't we do
SELECT enum_range(myenum)? What is the meaning of casting null? |
Try:
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'myenum'
3 Comments
Kev
If you have the same enum in more than one schema, this might need to be narrowed down a bit. If that's the case, see postgresql.org/docs/current/static/catalog-pg-type.html for details.
David Underhill
I think you need to prefix 'myenum' with an underscore. Check out my answer if you need to get enum values and the enum name may be used in more than one schema.
Clint Pachl
If enumeration order is important, append
ORDER BY e.enumsortorder to the query. The enumerated values will most likely be out of order if new values were inserted into the enumeration type using BEFORE or AFTER.In case someone is looking for the psql way then it's
\dT+ <your enum>
1 Comment
martti
It starts with backslash, actually.
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.
1 Comment
Heitor Chang
Casting to text allows you to sort the values
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'))
2 Comments
David Underhill
What do you mean that it is the array type? This works for me (PostgreSQL 9.0).
user9645
+1 This worked for me whereas @Kev's answer did not, due to the use of schemas in my DB.