Get the Best Enum Data Type in PostgreSQL

Introduction

Enum Data Type in PostgreSQL : PostgreSQL, a powerful and versatile relational database system, supports a wide variety of data types, including the Enum data type. Enums, short for enumerated types, are user-defined data types that consist of a static, ordered set of values. They are especially useful when a column should only accept a predefined set of values, improving data consistency.

In this guide, we’ll explore PostgreSQL Enum data types with practical examples, covering everything from inserting and updating enum values to retrieving enum types. Whether you’re just starting out or have some experience with PostgreSQL, this post will help you grasp the fundamentals of working with enums.

What is an Enum Data Type in PostgreSQL?

In PostgreSQL, the Enum data type allows you to define a column with a fixed set of possible values. These values are ordered and cannot be changed once the enum type is created, making it an excellent choice for fields where a limited set of predefined options is required (such as statuses or categories).

For example, if you’re building a task management system, the task status could be one of several predefined values like new, in_progress, or completed. Here’s how you define an enum type for task statuses:

CREATE TYPE task_status AS ENUM ('new', 'in_progress', 'completed');

PostgreSQL Insert Enum Value

Once an enum type is defined, you can use it in table creation, and the enum values can be inserted directly into the table. Here’s an example of how to create a table and insert enum values:

Create a Table Using an Enum Type

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    status task_status
);

Insert Enum Values

INSERT INTO tasks (name, status)
VALUES ('Fix Bugs', 'in_progress'),
       ('Write Documentation', 'new');

In this example, we’re inserting tasks with a specific status. Notice how easy it is to work with the enum values just by referencing the predefined values like in_progress and new.

PostgreSQL Update Enum Type

One limitation of enum types is that, by default, they are immutable. Once created, you cannot modify the enum type (e.g., adding or removing values) without some workarounds. However, PostgreSQL does provide a method for adding new values to an existing enum type.

Here’s an example of how to update an enum type by adding a new value:

Add a New Value to Enum

ALTER TYPE task_status ADD VALUE 'on_hold' AFTER 'in_progress';

This command adds the new value on_hold to the task_status enum after the in_progress value. You can specify the position using BEFORE or AFTER.

Note: You cannot remove or reorder enum values in PostgreSQL without dropping and recreating the enum type, which would require altering the table and losing data unless done carefully.

PostgreSQL Get Enum Values

Sometimes, you might want to retrieve all the possible values of an enum type programmatically. You can achieve this by querying the system catalogs in PostgreSQL.

Query to Get Enum Values

SELECT enumlabel
FROM pg_enum
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE pg_type.typname = 'task_status';

This query will return a list of all the values defined in the task_status enum.

Output Example:

enumlabel  
-------------
 new
 in_progress
 completed
 on_hold
(4 rows)

PostgreSQL Enum Type Example

Here’s a complete example that ties everything together, demonstrating the creation, insertion, updating, and querying of an enum type in PostgreSQL.

Full Example

Define Enum Type

CREATE TYPE task_status AS ENUM ('new', 'in_progress', 'completed');

Create Table Using Enum Type

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    status task_status
);

Insert Data

INSERT INTO tasks (name, status)
VALUES ('Design Database', 'new'),
       ('Develop Backend', 'in_progress'),
       ('Test Features', 'completed');

Add a New Enum Value

ALTER TYPE task_status ADD VALUE 'on_hold' AFTER 'in_progress';

Update a Task to Use the New Enum Value

UPDATE tasks
SET status = 'on_hold'
WHERE name = 'Develop Backend';

Retrieve All Enum Values

SELECT enumlabel
FROM pg_enum
JOIN pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE pg_type.typname = 'task_status';

Output:

enumlabel  
-------------
 new
 in_progress
 on_hold
 completed
(4 rows)

Conclusion

Working with Enum data types in PostgreSQL provides a powerful way to enforce data integrity and consistency. Enums ensure that only a predefined set of values can be inserted into specific columns, which is useful for managing states, categories, or other limited sets of data.

In this post, we explored the basics of creating, inserting, and updating enum values, as well as retrieving the defined values from the system. With these practical examples, you can now confidently use enum types in your PostgreSQL projects.

Enum Data Type in PostgreSQL with Practical Examples