When designing a PostgreSQL database, you often need to restrict a column to a specific set of allowed values.
For example, an order might have these statuses:
pending
processing
shipped
delivered
cancelled
PostgreSQL gives you several ways to enforce this rule. Two common approaches are:
ENUM
and:
CHECK Constraint
Both can protect your data, but they have different trade-offs.
In this guide, we’ll compare PostgreSQL ENUM vs CHECK constraints, including syntax, flexibility, performance, migrations, maintainability, and when you should choose each approach.
What Is a PostgreSQL ENUM?
An ENUM is a custom PostgreSQL data type that contains a predefined list of values.
For example:
CREATE TYPE order_status AS ENUM (
'pending',
'processing',
'shipped',
'delivered',
'cancelled'
);
You can then use it in a table:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status order_status NOT NULL
);
Now PostgreSQL only accepts values defined in the order_status type.
For example:
INSERT INTO orders (status)
VALUES ('pending');
works.
But:
INSERT INTO orders (status)
VALUES ('unknown');
fails because unknown isn’t part of the ENUM.
What Is a CHECK Constraint?
A CHECK constraint validates whether a condition is true before PostgreSQL accepts a row.
For example:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status TEXT NOT NULL
CHECK (
status IN (
'pending',
'processing',
'shipped',
'delivered',
'cancelled'
)
)
);
Now status is still stored as TEXT, but PostgreSQL enforces the allowed values.
INSERT INTO orders (status)
VALUES ('pending');
works.
But:
INSERT INTO orders (status)
VALUES ('unknown');
fails.
ENUM vs CHECK: Quick Comparison
| Feature | ENUM | CHECK Constraint |
|---|---|---|
| Defined as | Custom type | Table/column constraint |
| Restricts values | Yes | Yes |
| Easy to change | Moderate | Easy |
| Reusable | Yes | Usually defined per table |
| Migration flexibility | Lower | Higher |
| Supports complex rules | Limited | Excellent |
| Human-readable schema | Good | Excellent |
| Works with text values | No, custom type | Yes |
| Application evolution | Can require migrations | Usually easier |
| Best for | Stable domain values | Flexible business rules |
PostgreSQL ENUM Example
Let’s create an order status type:
CREATE TYPE order_status AS ENUM (
'pending',
'processing',
'shipped',
'delivered',
'cancelled'
);
Create the table:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status order_status NOT NULL
);
Insert valid data:
INSERT INTO orders (status)
VALUES ('pending');
PostgreSQL accepts it.
Invalid ENUM Value
Try:
INSERT INTO orders (status)
VALUES ('returned');
PostgreSQL rejects it because:
returned
isn’t part of the ENUM.
To add a value, you need to modify the type.
For example:
ALTER TYPE order_status
ADD VALUE 'returned';
Now it becomes valid.
CHECK Constraint Example
With a CHECK constraint:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status TEXT NOT NULL
CHECK (
status IN (
'pending',
'processing',
'shipped',
'delivered',
'cancelled'
)
)
);
The database still prevents invalid values.
But the allowed values are part of the table definition rather than a separate PostgreSQL type.
Adding a New Value to a CHECK Constraint
Suppose you want to add:
returned
You can replace the constraint.
For example:
ALTER TABLE orders
DROP CONSTRAINT orders_status_check;
Then:
ALTER TABLE orders
ADD CONSTRAINT orders_status_check
CHECK (
status IN (
'pending',
'processing',
'shipped',
'delivered',
'cancelled',
'returned'
)
);
This is more verbose than adding an ENUM value, but it provides more control over the constraint.
Which Is Easier to Change?
Generally:
CHECK Constraint
↓
More flexible
while:
ENUM
↓
More tightly defined
If your business requirements change frequently, CHECK constraints are often easier to evolve.
For example, if an application frequently changes:
pending
processing
approved
rejected
archived
a CHECK constraint can be easier to manage.
ENUM Is Good for Stable Values
ENUM works well when values are fundamental and unlikely to change frequently.
Examples:
day of week
fixed categories
known states
fixed priority levels
For example:
CREATE TYPE priority_level AS ENUM (
'low',
'medium',
'high'
);
If these values are part of a stable domain model, ENUM can be a clean choice.
CHECK Is Good for Business Rules
CHECK constraints become especially powerful when the validation isn’t simply a fixed list.
For example:
price NUMERIC(12,2)
CHECK (price >= 0)
Or:
age INTEGER
CHECK (age >= 18)
Or:
quantity INTEGER
CHECK (quantity > 0)
These rules cannot be represented by an ENUM.
Complex CHECK Constraints
CHECK constraints can express more complex conditions.
For example:
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
price NUMERIC(12,2) NOT NULL,
discount NUMERIC(12,2) NOT NULL,
CHECK (price >= 0),
CHECK (discount >= 0),
CHECK (discount <= price)
);
Now PostgreSQL guarantees:
price >= 0
discount >= 0
discount <= price
This is a major advantage of CHECK constraints.
ENUM and Type Safety
ENUM provides strong type semantics.
For example:
status order_status
clearly communicates:
This column represents an order status.
The type itself knows the allowed values.
This can make the schema easier to understand.
CHECK and Text Columns
With a CHECK constraint:
status TEXT
CHECK (status IN ('pending', 'completed'))
the column remains a normal text value.
This can make integration with applications and other tools straightforward.
For many applications, this flexibility is useful.
ENUM Reusability
One advantage of ENUM is that the same type can be reused across multiple tables.
For example:
CREATE TYPE priority_level AS ENUM (
'low',
'medium',
'high'
);
Then:
CREATE TABLE tasks (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
priority priority_level NOT NULL
);
And:
CREATE TABLE tickets (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
priority priority_level NOT NULL
);
Both tables use the same PostgreSQL type.
CHECK Constraint Reusability
A CHECK constraint is normally associated with a particular table or column.
For example:
CREATE TABLE tasks (
priority TEXT
CHECK (priority IN ('low', 'medium', 'high'))
);
If another table needs the same rule, you typically define another constraint.
This can lead to duplication.
ENUM vs CHECK for Shared Values
If several tables genuinely share the same stable domain:
low
medium
high
ENUM can provide a reusable type:
priority_level
But if the allowed values vary between tables, CHECK constraints can be more appropriate.
ENUM Migration Considerations
Database migrations are an important consideration.
Suppose your application currently has:
pending
processing
completed
and you need:
archived
You can add the ENUM value:
ALTER TYPE order_status
ADD VALUE 'archived';
However, changing or removing ENUM values can be more complicated than modifying a CHECK constraint.
This matters in applications with frequent schema changes.
Removing an ENUM Value
Adding an ENUM value is straightforward.
Removing one is more complicated.
For example, PostgreSQL doesn’t provide the same simple ALTER TYPE ... DROP VALUE operation for removing an ENUM value.
You may need to:
- Update existing rows.
- Create a replacement type.
- Change dependent columns.
- Drop the old type.
- Rename the replacement type.
This is one reason CHECK constraints are often preferred when the list is expected to change frequently.
CHECK Constraint Migration
CHECK constraints can generally be changed by:
Drop old constraint
↓
Create new constraint
For example:
ALTER TABLE orders
DROP CONSTRAINT orders_status_check;
Then:
ALTER TABLE orders
ADD CONSTRAINT orders_status_check
CHECK (
status IN (
'pending',
'processing',
'completed',
'archived'
)
);
This can be easier to manage in evolving applications.
ENUM Ordering
PostgreSQL ENUM values have an ordering based on their defined order.
For example:
CREATE TYPE priority AS ENUM (
'low',
'medium',
'high'
);
The order is:
low < medium < high
This can be useful when the ordering itself is meaningful.
However, don’t use ENUM ordering as a substitute for explicit business logic if the ordering rules are likely to change.
CHECK Constraint Ordering
A CHECK constraint doesn’t inherently provide ordering.
For example:
CHECK (
priority IN ('low', 'medium', 'high')
)
simply validates membership.
If you need priority ordering, you may instead store a numeric priority:
low → 1
medium → 2
high → 3
or use a separate lookup table.
ENUM Performance
ENUM values are stored efficiently and PostgreSQL handles them natively.
For most applications, the performance difference between ENUM and a text column with a CHECK constraint isn’t likely to be the deciding factor.
The bigger consideration is usually:
Maintainability
Schema evolution
Data modeling
Application requirements
Don’t choose ENUM purely because you expect it to be dramatically faster.
CHECK Constraint Performance
CHECK constraints are evaluated when rows are inserted or updated.
For example:
CHECK (quantity >= 0)
The overhead is generally small.
Again, the main reason to use CHECK is data integrity, not performance optimization.
ENUM vs CHECK for PostgreSQL APIs
Imagine an API:
POST /orders
with:
{
"status": "pending"
}
Both approaches can validate the value at the database level.
ENUM
API
↓
PostgreSQL
↓
order_status ENUM
CHECK
API
↓
PostgreSQL
↓
TEXT + CHECK
Both prevent invalid values from being stored.
ENUM vs CHECK with ORMs
ORMs such as Hibernate, Prisma, Sequelize, and TypeORM can support both patterns, but the exact behavior varies by framework and migration tooling.
For example, your application may define:
OrderStatus
as an application enum.
The database could represent it using:
PostgreSQL ENUM
or:
TEXT + CHECK
When choosing, consider how comfortably your ORM and migration system handle changes to PostgreSQL ENUM types.
When ENUM Can Become Difficult
Imagine an application with hundreds of deployments.
A product status starts as:
draft
published
archived
Later it becomes:
draft
review
approved
published
scheduled
archived
rejected
If these values change frequently, managing ENUM migrations can become cumbersome.
A CHECK constraint or lookup table may be easier.
When CHECK Can Become Difficult
CHECK constraints can also become problematic when you have many tables duplicating the same large list.
For example:
Table A
CHECK (...20 values...)
Table B
CHECK (...20 values...)
Table C
CHECK (...20 values...)
Now the same business rule exists in multiple places.
If the domain is stable and shared, an ENUM or lookup table may provide a cleaner model.
ENUM vs CHECK vs Lookup Table
There is actually a third option worth considering:
Lookup Table
For example:
CREATE TABLE order_statuses (
id SMALLINT PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);
Then:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status_id SMALLINT NOT NULL
REFERENCES order_statuses(id)
);
Now statuses are stored as rows.
Lookup Tables Are Useful for Dynamic Values
A lookup table is often better when values have their own data.
For example:
id
name
description
sort_order
is_active
created_at
Example:
1 | Pending
2 | Processing
3 | Shipped
4 | Delivered
5 | Cancelled
This allows administrators or applications to manage the values without changing the database type or constraint definition.
ENUM vs CHECK vs Lookup Table
| Feature | ENUM | CHECK | Lookup Table |
|---|---|---|---|
| Simple fixed values | Excellent | Excellent | Good |
| Frequently changing values | Less ideal | Good | Excellent |
| Complex validation | Limited | Excellent | Excellent |
| Reusable domain | Excellent | Limited | Excellent |
| Extra metadata | No | No | Yes |
| Easy application management | Moderate | Good | Excellent |
| Schema migration required for value changes | Usually | Usually constraint change | No, often data change |
| Foreign-key relationships | No | No | Yes |
Which Should You Choose?
Use this general rule:
Stable + Small Set
↓
ENUM
For example:
low
medium
high
If the rule is flexible or likely to change:
Business Rule
↓
CHECK Constraint
If values are dynamic and have additional information:
Dynamic Domain
↓
Lookup Table
Use ENUM When
ENUM is a good choice when:
✔ Values are stable
✔ The set is relatively small
✔ The same domain is used in multiple places
✔ Values represent a fundamental database concept
✔ You want a dedicated PostgreSQL type
Examples:
priority
day_of_week
fixed account state
Use CHECK When
CHECK constraints are a good choice when:
✔ Values may evolve
✔ Validation rules are more complex
✔ The rule belongs to one table
✔ You want simple migrations
✔ You want to keep the column as TEXT or another standard type
Examples:
price >= 0
quantity > 0
status IN (...)
discount <= price
Use a Lookup Table When
Use a lookup table when:
✔ Values change regularly
✔ Users/admins manage the values
✔ Values have descriptions or metadata
✔ You need foreign-key relationships
✔ You need custom ordering
✔ The list is part of business configuration
Examples:
product categories
countries
departments
custom statuses
payment methods
A Practical Example
Imagine an e-commerce application.
Order Status
Order statuses might be:
pending
processing
shipped
delivered
cancelled
If these are stable, ENUM can work:
CREATE TYPE order_status AS ENUM (
'pending',
'processing',
'shipped',
'delivered',
'cancelled'
);
Product Price
Price needs a business rule:
price NUMERIC(12,2)
CHECK (price >= 0)
ENUM isn’t appropriate.
Product Category
If categories are managed by administrators:
Electronics
Clothing
Books
Home
Sports
a lookup table is likely more appropriate.
This illustrates an important point:
The best database design may use ENUM, CHECK constraints, and lookup tables together.
You don’t need to choose one strategy for the entire database.
Combining ENUM and CHECK
You can also use both.
For example:
CREATE TYPE order_status AS ENUM (
'pending',
'processing',
'shipped',
'delivered',
'cancelled'
);
Then:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status order_status NOT NULL,
total NUMERIC(12,2) NOT NULL
CHECK (total >= 0)
);
Here:
ENUM
→ Controls order status
CHECK
→ Controls order total
This is a perfectly valid design.
A Common Mistake: Using ENUM Everywhere
ENUM is useful, but don’t turn every business category into an ENUM.
For example:
Product Category
Payment Provider
Shipping Method
Department
User Role
Order Status
Some of these values may change frequently or require additional metadata.
A lookup table can be much easier to maintain.
A Common Mistake: Using CHECK Everywhere
The opposite is also possible.
If the same stable domain appears across many tables, repeating:
CHECK (status IN (...))
can create unnecessary duplication.
A reusable ENUM or lookup table may provide a cleaner design.
A Common Mistake: Ignoring Application Migrations
Your database schema and application code must evolve together.
If you add:
returned
to a PostgreSQL ENUM, the application must also understand:
returned
Otherwise the database may accept a value that the application doesn’t handle correctly.
Schema changes should therefore be part of your deployment and migration strategy.
Best Practices
1. Use ENUM for Stable Domains
Don’t use it for values that change constantly.
2. Use CHECK for Business Rules
Especially when validation involves comparisons or multiple conditions.
3. Use Lookup Tables for Dynamic Configuration
If users or administrators need to manage values, a table is often better.
4. Keep Constraints in the Database
Don’t rely only on application validation.
5. Consider Migration Complexity
Think about how the values will evolve before choosing ENUM.
6. Use Consistent Modeling
Don’t represent the same domain as ENUM in one table and unrestricted TEXT in another without a good reason.
7. Don’t Optimize for Tiny Performance Differences
For most applications, maintainability and correctness matter more than small differences between ENUM and CHECK.
Decision Tree
Use this simple decision tree:
Are the values stable and fixed?
│
Yes
↓
Are they a
reusable domain?
/ \
Yes No
↓ ↓
ENUM CHECK
Are values dynamic or admin-managed?
│
Yes
↓
Lookup Table
For complex validation:
Complex Business Rule
↓
CHECK Constraint
Final Verdict
There is no universal winner between PostgreSQL ENUM and CHECK constraints.
Choose ENUM when:
The values are stable
+
The domain is well-defined
+
You want a reusable PostgreSQL type
Choose CHECK when:
The values may change
+
You want flexible constraints
+
You need more complex validation
Choose a Lookup Table when:
Values are dynamic
+
They have metadata
+
They need relationships
+
Administrators may manage them
A good PostgreSQL design might therefore look like:
Stable domain
↓
ENUM
Validation rule
↓
CHECK
Dynamic business data
↓
Lookup Table
The most important question isn’t:
“Is ENUM better than CHECK?”
It’s:
“How stable is this data, and how will the business rule evolve over time?”
If the answer is stable and fundamental, ENUM is a clean choice.
If the answer is likely to evolve, CHECK is usually more flexible.
If the answer is dynamic and data-driven, use a lookup table.
Choose the constraint strategy based on the lifecycle of your data, not just how the schema looks today.
