The query below lists all columns with enum data type in PostgreSQL database

Query

select col.table_schema,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.udt_name,
       string_agg(enu.enumlabel, ', ' 
                  order by enu.enumsortorder) as enum_values
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
join pg_type typ on col.udt_name = typ.typname
join pg_enum enu on typ.oid = enu.enumtypid
where col.table_schema not in ('information_schema', 'pg_catalog')
      and typ.typtype = 'e'
group by col.table_schema,
         col.table_name,
         col.ordinal_position,
         col.column_name,
         col.udt_name
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Columns

• table_schema - name of the schema
• table_name - name of the table
• column_id - column position in table
• column_name - name of the column
• udt_name - type of data
• enum_values - declared possible enum values

Rows

• One row represents one column with a enum type
• Scope of rows: all columns containing enum data types in the schema
• Ordered by schema name, table name and column position in table

Sample results