postgresql insert enum value

Mastering PostgreSQL Enum Data Type: A Complete Guide
When working with relational databases like PostgreSQL, one of the most powerful features developers can leverage is the Enum data type. Enums (short for "Enumerated types") allow for the creation of a custom data type consisting of a set of predefined values. In this comprehensive guide, we will walk you through everything you need to know about the Enum data type in PostgreSQL, including how to insert, update, and retrieve Enum values. Whether you're new to PostgreSQL or looking to expand your database management skills, this article will help you master PostgreSQL's Enum types.
What is the PostgreSQL Enum Data Type?
The Enum data type in PostgreSQL allows you to define a set of predefined values for a column. This can be incredibly useful when you want to restrict the values of a column to a specific set of possibilities, such as days of the week, user roles, or order statuses. By using Enum types, you can enforce data consistency and prevent invalid entries, which enhances the integrity of your database.
For instance, you could have an Enum type called order_status with values like pending, shipped, and delivered. This would ensure that only one of these values could be stored in that particular column, which can help prevent errors and streamline data management.
How to Create an Enum Type in PostgreSQL
Creating an Enum type in PostgreSQL is quite simple. You can define it using the CREATE TYPE statement. Let’s look at an example where we create an Enum type for order_status with the values pending, shipped, and delivered:
Once the Enum type is created, you can use it just like any other data type when defining your table structure.
PostgreSQL Enum Type Example: Creating a Table with Enum
Let’s create a table that uses the Enum type order_status in a column called status:
In this example, the status column is restricted to the values defined in the order_status Enum (pending, shipped, and delivered).
PostgreSQL Insert Enum Value
Once you've defined your Enum type and created a table, you can insert Enum values into the table. The process is straightforward. To insert a record into the orders table with a specific order_status, you use the INSERT statement, just like you would with any other data type:
In this example, we're inserting a new order record with the status of pending. Since pending is a valid value in the order_status Enum, this insertion will succeed without any issues.
If you attempt to insert a value that’s not part of the Enum (like 'canceled'), PostgreSQL will throw an error:
This feature of PostgreSQL ensures that only predefined values are accepted, improving data consistency and reducing potential errors.
PostgreSQL Update Enum Type
In many cases, you may need to update the Enum value for a column in a table. In PostgreSQL, updating an Enum value works like updating any other data type. Here’s an example of how to update the status of an order in the orders table:
In this case, we are changing the status of the order with order_id = 1 to 'shipped'. The updated value must be one of the predefined Enum values, or PostgreSQL will return an error.
If you need to update the Enum data type itself (for example, adding new values or removing old ones), you can use the ALTER TYPE statement.
Adding New Values to an Existing Enum
To add a new value to an existing Enum type, PostgreSQL provides the ALTER TYPE command. Let’s say we want to add a new value, returned, to the order_status Enum. Here’s how you would do it:
After running this command, returned becomes a valid option for the status column in the orders table, and you can use it like any other Enum value.
Retrieving Enum Values: PostgreSQL Get Enum Values
If you need to retrieve all possible values of an Enum type, PostgreSQL makes this easy with the enum_range() function. This function returns all the values of an Enum as an array. Here’s how you can use it to get the values of the order_status Enum:
This approach is particularly useful when you want to display the possible values for an Enum in a user interface or when performing data validation.
Best Practices When Using PostgreSQL Enums

1. Use Enums for Controlled Data: Enums are best used when you know in advance the set of values a column can take. They are particularly useful for representing categorical data, such as days of the week, order statuses, or user roles.
2. Avoid Overuse: While Enums are powerful, they should not be overused. If a column's value set might change frequently, a lookup table with foreign keys might be a better approach.
3. Consider Enum Limitations: Keep in mind that while Enum types help enforce data integrity, they are not as flexible as other methods like reference tables, especially when values need to be modified or extended frequently.
4. Company Name - CsharpMaster
5. City - Jaipur
6. State - Rajasthan
7. Country - India
8. Email - mailto:[e-mail]