Enhancing Data Integrity: How to Get Enum Values in PostgreSQL
In PostgreSQL, enums (enumerated types) offer a way to define a set of predefined string literals that a column can hold. This enforces data integrity by restricting the column's values to the explicitly listed options. It also improves code readability, as developers can use meaningful enum labels instead of raw string values.
Retrieving Enum Values with information_schema
PostgreSQL provides a built-in schema called information_schema
that stores information about the database itself, including details about user-defined data types like enums. To get all allowed values for a specific enum type, you can leverage the enum_range
function:
SELECT enum_label AS enum_value
FROM information_schema.enum_type et
WHERE et.enum_name = 'your_enum_type_name';
Explanation:
SELECT enum_label AS enum_value
: This part of the query selects theenum_label
column from the result set and renames it toenum_value
for clarity. Theenum_label
column stores the actual string values defined within the enum.FROM information_schema.enum_type et
: This specifies that we're retrieving data from theenum_type
table within theinformation_schema
schema. The aliaset
is used for convenience.WHERE et.enum_name = 'your_enum_type_name'
: This filters the results to only include rows where theenum_name
(the name you assigned to the enum type) matches the specifiedyour_enum_type_name
. Replace'your_enum_type_name'
with the actual name of your enum type.
Example Scenario
Suppose you have an enum type named order_status
defined with these possible values:
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'canceled');
Executing the Query
If you run the following query:
SELECT enum_label AS enum_value
FROM information_schema.enum_type et
WHERE et.enum_name = 'order_status';
You'll get the following output:
enum_value
----------
pending
shipped
delivered
canceled
This confirms that the order_status
enum can have the values pending
, shipped
, delivered
, and canceled
.
Assuming you have an order_status
enum type defined in your PostgreSQL database, here's how to retrieve its allowed values:
SELECT enum_label AS enum_value
FROM information_schema.enum_type et
WHERE et.enum_name = 'order_status';
SELECT enum_label AS enum_value
: This selects theenum_label
column and renames it toenum_value
for clarity.WHERE et.enum_name = 'order_status'
: This filters the results to only include rows where theenum_name
matches'order_status'
, which should be the actual name of your enum type.
Running the Query:
Execute this query in your PostgreSQL client tool. If the order_status
enum exists with defined values, you'll see the following output:
enum_value
----------
pending
shipped
delivered
canceled -- Assuming these are the defined values for order_status
PostgreSQL provides a built-in view named enum_typename_enum_vals
(where typename
is the actual name of your enum type). This view directly exposes the enum labels as separate columns. Here's how to use it:
SELECT *
FROM information_schema.enum_typename_enum_vals;
SELECT *
: This selects all columns from the view.FROM information_schema.enum_typename_enum_vals
: This specifies the view to retrieve data from. Replacetypename
with the actual name of your enum type.
Example:
Assuming your enum type is named order_status
, the query would be:
SELECT *
FROM information_schema.order_status_enum_vals;
This approach can be convenient if you want to access the enum values directly as separate columns without renaming. However, it requires memorizing the specific view name for each enum type.
Dynamic Query Construction (Less Recommended):
This method involves constructing a dynamic query string based on the enum name. It's generally less recommended due to potential for SQL injection vulnerabilities and reduced readability. However, it's included for completeness.
Steps:
- Retrieve enum name: Use system functions like
pg_catalog.current_schemas()
andpg_catalog.enum_types()
to get the list of enums and their names within your current schema. - Build query string: Construct a string with the table and column names of the
information_schema.enum_type
table, filtering by the retrieved enum name. - Execute dynamic query: Use functions like
EXECUTE
to dynamically execute the constructed string as a query.
Important Note:
- Security: Be cautious when using dynamic queries. Ensure proper input validation and escaping mechanisms to prevent SQL injection vulnerabilities.
- Readability: This method can make code less readable compared to the information_schema approaches.
postgresql enums