2024-02-22

Taming the ENUM Jungle: A Comprehensive Guide to Listing Values in PostgreSQL

postgresql Listing Postgres ENUM Types: Your Guide with Examples

Methods for Listing ENUM Values:

1. Using pg_enum System Table:

This built-in table stores information about all ENUM types in your database. You can query it with:

SELECT enumlabel
FROM pg_enum
WHERE enumtypid = (SELECT pg_type.oid
                   FROM pg_type
                   INNER JOIN information_schema.columns
                     ON pg_type.typname = information_schema.columns.data_type
                   WHERE information_schema.columns.column_name = 'your_column_name'
                   AND information_schema.columns.table_name = 'your_table_name');
  • Replace your_column_name with the actual column name.
  • Replace your_table_name with the actual table name.

2. Using Information Schema:

While less flexible, this method offers a simpler version:

SELECT data_type
FROM information_schema.columns
WHERE column_name = 'your_column_name'
AND table_name = 'your_table_name';

This returns the ENUM type name, not individual values.

3. Using enum_range Function:

This function returns a range of allowed values for an ENUM, but not directly the labels. However, you can combine it with unnest:

SELECT unnest(enum_range('your_enum_type_name')) AS value;

Example:

Suppose you have a table named orders with a column named status of type order_status:

CREATE TYPE order_status AS ENUM ('placed', 'shipped', 'delivered', 'canceled');

Listing values with pg_enum:

SELECT enumlabel
FROM pg_enum
WHERE enumtypid = (SELECT pg_type.oid
                   FROM pg_type
                   INNER JOIN information_schema.columns
                     ON pg_type.typname = information_schema.columns.data_type
                   WHERE information_schema.columns.column_name = 'status'
                   AND information_schema.columns.table_name = 'orders');

This will output:

  enumlabel
-----------
 placed
 shipped
 delivered
 canceled

Related Issues and Solutions:

  • Security: Always specify the full table and column names in your queries to avoid unintended operations.
  • Performance: For large tables, consider using pg_enum only for occasional checks, not frequent queries.
  • Understanding Values: If you only have the ENUM type name, use SELECT * FROM pg_catalog.pg_enum WHERE enumtypid = <type_oid> to get details like labels and order.

I hope this explanation, with examples, helps you list Postgres ENUM types effectively!