How to Simulate an Enum Using SMALLINT in PostgreSQL

When modeling a small set of predefined values in PostgreSQL, you have several options:

• PostgreSQL’s native ENUM type
• A SMALLINT column with a CHECK constraint
• A SMALLINT column with a foreign key to a lookup table
• A plain SMALLINT column validated only by application code

Each approach has different trade-offs in terms of type safety, flexibility, performance, and maintainability.

This article compares them and explains when to use each one.

The Problem#

Suppose an order can have one of three statuses:

0 = Pending
1 = Paid
2 = Cancelled

We want to store the status efficiently while preventing invalid values such as:

99
-1
1234

The question is: how should we model this in PostgreSQL?

Option 1: SMALLINT with a CHECK Constraint#

The simplest approach is to store the enum value as a SMALLINT and restrict the allowed values with a CHECK constraint.

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,

    status SMALLINT NOT NULL
        CHECK (status IN (0, 1, 2))
);

The application can define the meaning:

0 = Pending
1 = Paid
2 = Cancelled

Invalid values are rejected by PostgreSQL:

INSERT INTO orders (status)
VALUES (99);

ERROR: new row violates check constraint

Advantages#

• Simple
• Efficient
• Fully enforced by the database
• No additional table required
• Easy to query

SELECT *
FROM orders
WHERE status = 1;

Disadvantages#

The meaning of the values is not visible from the column itself:

status = 1

What does 1 mean?

The application code or documentation must define that.

Another issue is that changing the allowed values requires modifying the table constraint:

ALTER TABLE orders
DROP CONSTRAINT orders_status_check;

ALTER TABLE orders
ADD CONSTRAINT orders_status_check
CHECK (status IN (0, 1, 2, 3));

For a small, stable set of values, however, this is often an excellent solution.

Option 2: SMALLINT Without a CHECK Constraint#

You can also simply define:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status SMALLINT NOT NULL
);

The application defines the values:

0 = Pending
1 = Paid
2 = Cancelled

This is the simplest database schema.

Advantages#

• Minimal schema
• No constraint maintenance
• Very flexible
• Very small storage size

Disadvantages#

The database accepts every valid SMALLINT value:

INSERT INTO orders (status)
VALUES (32767);

This succeeds.

The database has no idea whether 32767 is a valid business status.

Therefore, this approach relies entirely on application-level validation.

That can be acceptable when:

• There is only one application writing to the database
• The database is treated as an internal implementation detail
• Validation is centralized
• You intentionally want the database schema to remain permissive

However, it can become dangerous when multiple services, scripts, or administrators write directly to the database.

Option 3: SMALLINT with a Foreign Key#

A powerful alternative is to use a lookup table.

CREATE TABLE order_status (
    id SMALLINT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

Insert the valid statuses:

INSERT INTO order_status (id, name)
VALUES
    (0, 'pending'),
    (1, 'paid'),
    (2, 'cancelled');

Then reference the table:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,

    status SMALLINT NOT NULL
        REFERENCES order_status(id)
);

Now PostgreSQL prevents invalid values:

INSERT INTO orders (status)
VALUES (99);

This fails because 99 does not exist in order_status.

Advantages#

• No CHECK constraint
• Database-level validation
• The meaning of each value is stored in the database
• Easy to add metadata
• Easy to extend

For example:

CREATE TABLE order_status (
    id SMALLINT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    sort_order SMALLINT NOT NULL
);

Now the status table can contain business metadata.

Disadvantages#

• Requires an additional table
• Requires a foreign key
• Slightly more complex schema
• Requires managing reference data

This is usually the best option when you want a SMALLINT representation but still want strong database-level validation without using a CHECK constraint.

Option 4: PostgreSQL’s Native ENUM#

PostgreSQL also provides a native enum type:

CREATE TYPE order_status AS ENUM (
    'pending',
    'paid',
    'cancelled'
);

Then:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status order_status NOT NULL
);

The database now understands the valid values directly:

INSERT INTO orders (status)
VALUES ('paid');

Invalid values are rejected:

INSERT INTO orders (status)
VALUES ('unknown');

Advantages#

• Strong type safety
• Very readable
• Database-enforced validation
• No numeric mapping required

This is easy to understand:

status = 'paid'

compared with:

status = 1

Disadvantages#

PostgreSQL enums can be less flexible when the set of values changes.

Adding a value is possible:

ALTER TYPE order_status
ADD VALUE 'refunded';

But removing or renaming enum values has historically been more complicated than modifying rows in a lookup table.

This makes native enums a good choice for values that are truly part of the database’s stable domain model.

Comparison#

Which One Should You Choose?#

Use SMALLINT + CHECK for a small, stable set#

status SMALLINT NOT NULL
    CHECK (status IN (0, 1, 2))

This is a good default when:

• The values are stable
• You want compact storage
• You want database validation
• You do not need additional metadata

Use SMALLINT + foreign key when you want extensibility#

status SMALLINT NOT NULL
    REFERENCES order_status(id)

Choose this when:

• Statuses may change
• You need descriptions or metadata
• You want to enable or disable statuses
• You want to avoid a CHECK constraint
• The values are managed as reference data

This is often the most flexible design.

Use PostgreSQL ENUM for a stable domain type#

CREATE TYPE order_status AS ENUM (
    'pending',
    'paid',
    'cancelled'
);

This is a good choice when:

• The set of values is small
• The values are fundamental to the domain
• You want readable SQL
• You do not expect frequent changes

Use plain SMALLINT only when application validation is intentional#

status SMALLINT NOT NULL

This can be perfectly valid, but it is important to understand the trade-off:

The database will not know what values are valid.

This approach works best when validation is centralized in application code and direct database writes are tightly controlled.

A Practical Recommendation#

For most applications, I would choose one of these two designs:

Stable values#

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status SMALLINT NOT NULL
        CHECK (status IN (0, 1, 2))
);

Dynamic or metadata-rich values#

CREATE TABLE order_status (
    id SMALLINT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    status SMALLINT NOT NULL
        REFERENCES order_status(id)
);

The key distinction is simple:

Use a CHECK constraint when the valid values are part of the table’s invariant. Use a lookup table when the valid values are data that may evolve. Use a PostgreSQL ENUM when the values are a stable database-level type.

And if you choose a plain SMALLINT without any database constraint, do so deliberately—because you are moving responsibility for data integrity from PostgreSQL to the application.

References#

• PostgreSQL Documentation ↗
• PostgreSQL ENUM Type ↗
• PostgreSQL CHECK Constraints ↗
• PostgreSQL Foreign Keys ↗