List PostgreSQL Enum Types and Their Values with SQL

Apr 6, 2026 / Apr 8, 2026 · 4 min read · postgresql administration schema database data types pg_type pg_enum information_schema pg_namespace ·

Share on:

How to List All Enum Types in a PostgreSQL Database

PostgreSQL supports user-defined enum types — a fixed ordered set of string values stored efficiently as integers. Enums are common in application schemas for columns like status, role, or priority. Once created, their allowed values are managed in the database catalog, not in application code.

Over time, a database can accumulate many enum types, some with values that no longer match what the application uses. This SQL script queries pg_type and pg_enum to list every enum type in the current database along with all of its allowed values.

SQL Script

 1SELECT
 2    n.nspname          AS schema_name,
 3    t.typname          AS enum_name,
 4    e.enumlabel        AS enum_value,
 5    e.enumsortorder    AS sort_order
 6FROM
 7    pg_type t
 8    JOIN pg_enum e
 9        ON e.enumtypid = t.oid
10    JOIN pg_namespace n
11        ON n.oid = t.typnamespace
12WHERE
13    n.nspname NOT IN ('pg_catalog', 'information_schema')
14ORDER BY
15    n.nspname,
16    t.typname,
17    e.enumsortorder;

Notes: Works on PostgreSQL 8.3 and later, when pg_enum was introduced. Returns one row per enum value. To see one row per enum type with all values aggregated, use array_agg(e.enumlabel ORDER BY e.enumsortorder).

Code Breakdown

pg_type

The PostgreSQL system catalog table that stores information about every data type — both built-in types and user-defined types. The join to pg_enum implicitly filters to only enum types.

pg_enum

Stores one row for each value of each enum type. Key columns:

enumtypid — the OID of the parent type in pg_type
enumlabel — the text value (e.g. 'active', 'inactive')
enumsortorder — a float that defines the display order of values

pg_namespace

Stores schema names. Joined on t.typnamespace = n.oid to resolve the schema each enum type belongs to.

JOIN pg_enum ON e.enumtypid = t.oid

Links each enum value row back to its type. Because only enum types have rows in pg_enum, this join acts as an implicit filter for enum types only.

WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')

Excludes system schemas. All built-in PostgreSQL types live in pg_catalog. This filter returns only user-defined enums.

ORDER BY schema, type name, sort_order

Groups all values for the same enum together and lists them in the order they were defined, matching the order PostgreSQL uses for comparisons and sorting.

Key Points

Enum values are case-sensitive. 'Active' and 'active' are different values.
The enumsortorder column determines how PostgreSQL sorts and compares enum values. The value defined first is the "smallest".
Enum types are schema-scoped. Two schemas can have different enum types with the same name.
Adding a new value to an existing enum requires ALTER TYPE ... ADD VALUE, not a full type recreate.

Insights and Explanations

Enum types offer storage efficiency, enforced value constraints, and natural ordering — but they come with operational tradeoffs:

Adding values is easy; removing them is not. You can add a new value with ALTER TYPE name ADD VALUE 'new_value'. But you cannot remove a value without dropping and recreating the type, which requires dropping every column that uses it.
Schema migrations are risky. If your application tries to insert a value that does not exist in the enum, PostgreSQL raises an error. Deploy new enum values to the database before deploying the application code that writes them.
Enum values have a defined sort order. Queries using ORDER BY on an enum column sort by declaration order, not alphabetically. This is intentional but surprises developers who expect alphabetical sorting.

To see a compact summary with all values per enum on one line:

 1SELECT
 2    n.nspname      AS schema_name,
 3    t.typname      AS enum_name,
 4    array_agg(e.enumlabel ORDER BY e.enumsortorder) AS values
 5FROM
 6    pg_type t
 7    JOIN pg_enum e ON e.enumtypid = t.oid
 8    JOIN pg_namespace n ON n.oid = t.typnamespace
 9WHERE
10    n.nspname NOT IN ('pg_catalog', 'information_schema')
11GROUP BY
12    n.nspname,
13    t.typname
14ORDER BY
15    n.nspname,
16    t.typname;

To find which tables and columns use a specific enum type:

 1SELECT
 2    c.table_schema,
 3    c.table_name,
 4    c.column_name
 5FROM
 6    information_schema.columns c
 7WHERE
 8    c.udt_name = 'your_enum_name'
 9ORDER BY
10    c.table_schema,
11    c.table_name,
12    c.column_name;

Additional Considerations

Permissions: Any user can query pg_type, pg_enum, and pg_namespace. No superuser privilege is required.
Domains over enums: Some schemas use CREATE DOMAIN instead of CREATE TYPE ... AS ENUM. Domains do not appear in pg_enum. Check pg_type where typtype = 'd' to list domains.
Enum OIDs across databases: Enum type OIDs are not shared across databases. The same type name in two databases will have different OIDs.