Listing Postgres ENUM Types: Understanding Your Database's Defined Choices

In PostgreSQL, an ENUM type defines a set of allowed, predefined values for a column. This ensures data integrity by restricting input to specific options. It's useful for representing things like order statuses (pending, shipped, cancelled), days of the week, or any scenario where a limited set of choices applies.

Listing ENUM Types

There are two main ways to list the available ENUM types in your PostgreSQL database:

Using the pg_catalog.pg_type system catalog view:

This view provides information about all data types, including ENUMs. Here's the query:

SELECT typname
FROM pg_catalog.pg_type
WHERE typtype = 'e';

• pg_catalog.pg_type: This is a system catalog view that contains information about data types in the database.
• typname: This column in the view stores the name of the data type.
• WHERE typtype = 'e': This filters the results to only include ENUM types, identified by the code 'e'.

Using the pg_enum system catalog view:

This view specifically focuses on ENUM types, providing details about each one:

SELECT enumtypid AS type_oid,
       enumtypeName AS type_name,
       enumspace AS type_schema,
       enumlabel AS enum_value
FROM pg_catalog.pg_enum
ORDER BY enumtypid, enumspace, enumlabel;

• pg_catalog.pg_enum: This system catalog view stores information about ENUM types.
• enumtypid: This column uniquely identifies the ENUM type.
• enumtypeName: This column stores the name of the ENUM type.
• enumspace: This column indicates the schema (namespace) where the ENUM type is defined.
• enumlabel: This column lists the individual values (labels) within the ENUM type.
• ORDER BY enumtypid, enumspace, enumlabel: This clause sorts the output by ENUM type ID, schema, and then the labels themselves, providing a well-organized presentation.

Example Output

Assuming you have an ENUM type named order_status with values 'pending', 'shipped', and 'cancelled', running either query would produce output similar to:

+----------+
| type_name |
+----------+
| order_status |
+----------+

(for query 1)

or

+--------+----------+----------+-----------+
| type_oid | type_name | type_schema | enum_value |
+--------+----------+----------+-----------+
|    1234 | order_status | public  | pending   |
|    1234 | order_status | public  | shipped   |
|    1234 | order_status | public  | cancelled |
+--------+----------+----------+-----------+

Choose the query that best suits your needs:

• Use query 1 for a quick overview of all ENUM type names.
• Use query 2 for a more detailed listing, including schema and individual values within each ENUM type.

SELECT typname
FROM pg_catalog.pg_type
WHERE typtype = 'e';

SELECT enumtypid AS type_oid,
       enumtypeName AS type_name,
       enumspace AS type_schema,
       enumlabel AS enum_value
FROM pg_catalog.pg_enum
ORDER BY enumtypid, enumspace, enumlabel;

Explanation:

• Method 1: This query retrieves the typname (type name) from the pg_catalog.pg_type system catalog view. The WHERE clause filters the results to include only ENUM types, identified by the code 'e'.
• Method 2: This query delves deeper into the pg_catalog.pg_enum system catalog view. It extracts various details about each ENUM type:
  • enumtypid AS type_oid: Unique identifier of the ENUM type.
  • enumtypeName AS type_name: Name of the ENUM type.
  • ORDER BY enumtypid, enumspace, enumlabel: Sorts the output for clarity, first by type ID, then schema, and lastly by the label values themselves.

Running the Code:

1. Connect to your PostgreSQL database using a client like pgAdmin or the command line with psql.
2. Paste the desired query (Method 1 or 2) into the client's query window.
3. Execute the query.

PostgreSQL offers information schema views that provide metadata about database objects, but unfortunately, support for ENUM types in these views is limited. While some versions might have views like information_schema.enum_types, their functionality and reliability can vary. It's generally not recommended to rely on information schema views for ENUM type information unless you're certain your specific PostgreSQL version supports them and you understand any limitations.

Custom Function (Advanced):

For a more dynamic approach, you could potentially create a custom function that queries the system catalog views (pg_catalog.pg_type and pg_catalog.pg_enum) and processes the data to return a desired format (e.g., an array of ENUM type names or a dictionary of type names with their allowed values). This approach would require writing PL/pgSQL code and might be more complex to maintain, but it could offer some flexibility in terms of customizing the output.

Here's a very basic example of a PL/pgSQL function that retrieves ENUM type names (assuming you're comfortable with this approach):

CREATE OR REPLACE FUNCTION get_enum_types()
RETURNS text[] AS $$
DECLARE
  record pg_catalog.pg_type%ROW;
  enum_types text[];
BEGIN
  enum_types := '{}';

  FOR record IN SELECT typname FROM pg_catalog.pg_type
                 WHERE typtype = 'e'
  LOOP
    enum_types := enum_types || quote_literal(record.typname);
  END LOOP;

  RETURN enum_types;
END;
$$ LANGUAGE plpgsql;

This function is for illustrative purposes only and would need further development for practical use. It's important to weigh the complexity of this approach against the simplicity of using the system catalog views directly.