Enum Type in PostgreSQL: Definition and Usage

In PostgreSQL, the enum type is a special data type that allows you to define a list of allowed values for a column. It helps ensure that only predefined values can be stored, improving data consistency and readability.

How It Works

Think of the enum type as a list of fixed options you can choose from, like a menu at a restaurant. Instead of typing anything you want, you pick from the allowed choices. This makes sure the data stays clean and predictable.

When you create an enum type in PostgreSQL, you define all the possible values it can hold. Then, when you use this type for a column, PostgreSQL only lets you store those values. If you try to insert something else, it will give an error.

This is useful because it prevents mistakes like typos or inconsistent naming, which can happen if you use plain text columns. It also makes your data easier to understand and work with.

Example

This example shows how to create an enum type for a column that stores the status of an order. The allowed statuses are 'pending', 'shipped', and 'delivered'.

sql

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  product_name TEXT NOT NULL,
  status order_status NOT NULL
);

INSERT INTO orders (product_name, status) VALUES ('Book', 'pending');

SELECT * FROM orders;

Output

 id | product_name | status 
----+--------------+---------
  1 | Book         | pending
(1 row)

When to Use

Use the enum type when you have a column that should only hold a limited set of values, like status, categories, or types. It helps keep your data consistent and prevents invalid entries.

For example, you might use enum for user roles (admin, user, guest), payment methods (cash, credit, debit), or product sizes (small, medium, large). This makes your database easier to maintain and your queries more reliable.

Key Points

• Enum types restrict a column to a fixed set of values.
• They improve data integrity by preventing invalid entries.
• Enums make your data easier to read and understand.
• You define enums once and reuse them in multiple tables.
• Trying to insert a value not in the enum list causes an error.

Key Takeaways

PostgreSQL enum type limits a column to predefined values for better data consistency.

Use enum when you want to restrict data to a fixed set of meaningful options.

Enums prevent errors like typos and make data easier to understand.

You create an enum type once and then use it in table columns.

Inserting a value outside the enum list will cause an error.