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?
Asked
Active
Viewed 1.4e+01k times
209
-
I have also found the following answer very helpful: http://stackoverflow.com/questions/9540681/list-postgres-enum-type – anonymous coward Sep 18 '13 at 15:21
5 Answers
397
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.
Chris L
- 4,270
- 1
- 17
- 12
-
1
-
4The 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 – Justin Ohms Aug 29 '13 at 03:25
-
1To understand more about enum functions you can view this link https://www.postgresql.org/docs/8.3/static/functions-enum.html https://www.postgresql.org/docs/9.2/static/functions-array.html – Bikal Basnet Aug 25 '16 at 07:13
-
2
-
::myenum is a type cast to myenum. 'value1'::myenum is casting a string to enum. NULL::myenum is casting NULL to myenum. – Chris L Apr 17 '18 at 17:42
-
3@ChrisL thanks. it seems very strange. why can't we do `SELECT enum_range(myenum)`? What is the meaning of casting `null`? – mc9 Jul 24 '18 at 00:45
-
Sorry I am a beginner. I type exactly ```SELECT enum_range(NULL::myenum)``` and i received 'type "myenum" does not exist'. Am i supposed to replace certain words with my actual table "Role" or the enum column "type"? – Sydney Aug 19 '18 at 02:13
-
-
How can I use enum in a WHERE clause ? e.g: select * from public."table" where public."table"."enumtypecolumn" = "Enum value" – Varun Oct 29 '21 at 08:52
43
Try:
SELECT e.enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'myenum'
Kev
- 15,899
- 15
- 79
- 112
-
1If 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 http://www.postgresql.org/docs/current/static/catalog-pg-type.html for details. – Kev Oct 23 '09 at 22:03
-
1I 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. – David Underhill Jan 10 '11 at 09:24
-
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`. – Clint Pachl Sep 16 '15 at 23:26
11
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.
Justin Ohms
- 3,334
- 31
- 45
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'))
David Underhill
- 15,896
- 7
- 53
- 61
-
1What do you mean that it is the array type? This works for me (PostgreSQL 9.0). – David Underhill Jan 10 '11 at 17:36
-
+1 This worked for me whereas @Kev's answer did not, due to the use of schemas in my DB. – user9645 Jul 10 '13 at 13:39
