list Postgres ENUM type
PostgresqlPostgresql Problem Overview
The suggested query to list ENUM types is great. But, it merely lists of the schema
and the typname
. How do I list out the actual ENUM values? For example, in the linked answer above, I would want the following result
schema type values
------------- -------- -------
communication channels 'text_message','email','phone_call','broadcast'
Postgresql Solutions
Solution 1 - Postgresql
select n.nspname as enum_schema,
t.typname as enum_name,
e.enumlabel as enum_value
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n ON n.oid = t.typnamespace;
Solution 2 - Postgresql
You can list the data type via
\dT+ channels
https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS
Solution 3 - Postgresql
select enum_range(null::my_enum)
where my_enum
is the enum type name.
Documentation: http://www.postgresql.org/docs/9.5/static/functions-enum.html
Solution 4 - Postgresql
This: SELECT unnest(enum_range(NULL::myenum))
returns enum types as rows.
Solution 5 - Postgresql
I always forget how to do this. As per the other answer and the comment, here it is a comma-separated list. I like copy-paste snippets. Thanks for the help:
select n.nspname as enum_schema,
t.typname as enum_name,
string_agg(e.enumlabel, ', ') as enum_value
from pg_type t
join pg_enum e on t.oid = e.enumtypid
join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by enum_schema, enum_name;
Solution 6 - Postgresql
SELECT enum_range(NULL::myenum)
Solution 7 - Postgresql
@dpb:
If you want to create a permanent easy access method for this, you could always create a view
CREATE OR REPLACE VIEW oublic.enumz AS
SELECT n.nspname AS enum_schema,
t.typname AS enum_name,
e.enumlabel AS enum_value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_namespace n ON n.oid = t.typnamespace;
You could then create a trigger for the insert command.
The above will store this in the database for future reference purposes.
Solution 8 - Postgresql
This lists all enum-typed columns and their potential values:
SELECT
table_schema || '.' || table_name || '.' || column_name as field_name,
pg_enum.enumlabel as value
FROM pg_type
JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid
JOIN pg_namespace on pg_type.typnamespace = pg_namespace.oid
JOIN information_schema.columns ON (information_schema.columns.udt_name = pg_type.typname AND information_schema.columns.udt_schema = pg_namespace.nspname)
WHERE pg_type.typtype = 'e'
ORDER BY field_name, pg_enum.enumsortorder;
Solution 9 - Postgresql
Add order
SELECT
n.nspname AS enum_schema,
t.typname AS enum_name,
e.enumlabel AS enum_value
FROM
pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
ORDER BY
enum_name,
e.enumsortorder;
Solution 10 - Postgresql
If you have the table and column name, (but not the type name) use this:
SELECT pg_enum.enumlabel
FROM pg_type
JOIN pg_enum ON pg_enum.enumtypid = pg_type.oid
JOIN information_schema.columns ON information_schema.columns.udt_name =
pg_type.typname
WHERE pg_type.typtype = 'e' AND
table_name = $1 AND column_name = $2 ORDER BY pg_enum.enumsortorder
If you use enum_range
on a column (in contrast to the other answers which used it on a type) it will return data for each row that exists, which is not what you want. So use the above query instead.