List Postgres Type ENUM

Question

a suggested query for displaying ENUM types . But these are just schema and typname . How can I list the actual ENUM values? For example, in the related answer above, I would like to get the following result

 schema type values ------------- -------- ------- communication channels 'text_message','email','phone_call','broadcast' 

Answer 1

 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 

Answer 2

 select enum_range(enum_first(null::province),null::province); 

Answer 3

You can specify the data type through

 \dT+ channels 

https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS

Answer 4

I always forget how to do it. According to another answer and comment, here is a comma separated list. I like copy-paste fragments. 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 

Answer 5

@dpb:

If you want to create a simple easy access method for this, you can 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; 

Then you can create a trigger for the insert command.

The above will store this in a database for future reference purposes.

Answer 6

All listed columns and their potential values are listed here :

 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; 

Answer 7

If you have a table and column name (but not a 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 in a column (unlike the other answers that used it for the type), it will return data for every existing row, which you don't want. Therefore, use the above query.