Understanding Enum Sorting Behavior in PostgreSQL

Blog

July 18, 2025·

This article explains how PostgreSQL handles sorting of enum values and why the ordinal position of enum values matters when ordering results.

Introduction

PostgreSQL enums are a powerful feature that allows you to define a custom data type with a fixed set of values. However, there is a critical aspect of enums that often catches developers by surprise: the order of values is determined by their ordinal position (the order in which they were defined), not alphabetically or by any other criteria.

I discovered this quirk while writing a test case for a feature at work. My test was failing because I expected the enum values to be sorted alphabetically, but PostgreSQL had other plans. This led me down a rabbit hole of understanding how enum ordering actually works.

When you perform an ORDER BY query on an enum column, PostgreSQL sorts the values based on their ordinal position within the enum type definition. This behavior is fundamental to how enums work and can lead to unexpected results if you are not aware of it.

Quick Workaround

If you need to sort enum values alphabetically or by some other criteria instead of their ordinal position, you can cast the enum to text:

SELECT name, status 
FROM tasks 
ORDER BY status::text;

This converts the enum values to text and sorts them alphabetically. However, understanding the native enum sorting behavior is still important because it affects comparisons, indexes, and other database operations. Let’s explore how it actually works.

Creating an Enum Type

Let’s start with a simple example. Suppose we want to create an enum type to represent the status of a task:

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

In this definition:

pending has ordinal position 0
in_progress has ordinal position 1
completed has ordinal position 2
cancelled has ordinal position 3

You can verify the ordinal positions of enum values by querying the PostgreSQL system catalogs:

SELECT 
  enumlabel AS value, 
  enumsortorder AS ordinal
FROM 
  pg_enum
JOIN 
  pg_type ON pg_enum.enumtypid = pg_type.oid
WHERE 
  pg_type.typname = 'task_status'
ORDER BY 
  enumsortorder;

This will output:

    value     | ordinal
--------------+---------
 pending      |       1
 in_progress  |       2
 completed    |       3
 cancelled    |       4

Example 1: Basic Enum Sorting

Let’s create a table and insert some data:

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    status task_status NOT NULL
);

INSERT INTO tasks (name, status) VALUES
    ('Write documentation', 'completed'),
    ('Fix bug in login', 'in_progress'),
    ('Deploy to production', 'pending'),
    ('Review pull request', 'in_progress'),
    ('Update dependencies', 'cancelled');

Now, let’s query the tasks ordered by status:

SELECT name, status FROM tasks ORDER BY status;

The result will be:

         name          |   status
-----------------------+-------------
 Deploy to production  | pending
 Fix bug in login      | in_progress
 Review pull request   | in_progress
 Write documentation   | completed
 Update dependencies   | cancelled

Notice that the results are ordered by the ordinal position of the enum values, not alphabetically. The order follows: pending (0), in_progress (1), completed (2), cancelled (3).

Example 2: The Importance of Definition Order

The order in which you define enum values is permanent and affects all sorting operations. Let’s say we want to create a priority enum for our tasks:

CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high', 'critical');

If we add a priority column to our tasks table:

ALTER TABLE tasks ADD COLUMN priority task_priority DEFAULT 'medium';

UPDATE tasks SET priority = 'high' WHERE name = 'Fix bug in login';
UPDATE tasks SET priority = 'critical' WHERE name = 'Deploy to production';
UPDATE tasks SET priority = 'low' WHERE name = 'Update dependencies';

When we order by priority:

SELECT name, priority FROM tasks ORDER BY priority;

The result will be:

         name          | priority
-----------------------+----------
 Write documentation   | low
 Update dependencies   | low
 Review pull request   | medium
 Fix bug in login      | high
 Deploy to production  | critical

The sorting follows the definition order: low (0), medium (1), high (2), critical (3).

Example 3: What Happens When You Need to Reorder?

Here’s where things get tricky. Suppose you initially defined the priority enum incorrectly:

CREATE TYPE wrong_priority AS ENUM ('critical', 'high', 'medium', 'low');

With this definition, when you ORDER BY priority, the results would be sorted from critical to low, which is the opposite of what you probably want.

Unfortunately, PostgreSQL does not allow you to reorder existing enum values. The ordinal positions are fixed once the enum is created. Your options are:

Drop and recreate the enum type (requires dropping all columns using it first)
Create a new enum with the correct order and migrate your data
Use a custom sort expression to work around the issue

Let’s see how option 3 works:

SELECT name, priority 
FROM tasks 
ORDER BY 
    CASE priority
        WHEN 'low' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'high' THEN 3
        WHEN 'critical' THEN 4
    END;

This approach gives you control over the sort order, but it is verbose and needs to be applied wherever you want custom sorting.

Example 4: Adding New Enum Values

You can add new values to an existing enum type using the ALTER TYPE command. However, the position at which you add the value matters:

-- Add a new value at the end
ALTER TYPE task_status ADD VALUE 'on_hold';

-- Add a new value before an existing value
ALTER TYPE task_status ADD VALUE 'review' BEFORE 'completed';

-- Add a new value after an existing value
ALTER TYPE task_status ADD VALUE 'blocked' AFTER 'in_progress';

After these additions, the ordinal positions would be:

pending (0)
in_progress (1)
blocked (2)
review (3)
completed (4)
cancelled (5)
on_hold (6)

Any ORDER BY status query will now use this new ordering.

Example 5: Comparing Enum Values

Enum values can be compared using standard comparison operators (<, >, <=, >=, =, !=). The comparison is based on ordinal positions:

-- Find all tasks that are not yet completed
SELECT name, status 
FROM tasks 
WHERE status < 'completed';

This query returns tasks with status pending or in_progress because their ordinal positions are less than completed.

         name         |   status
----------------------+-------------
 Fix bug in login     | in_progress
 Deploy to production | pending
 Review pull request  | in_progress

Similarly:

-- Find all high priority or critical tasks
SELECT name, priority 
FROM tasks 
WHERE priority >= 'high';

This returns:

         name         | priority
----------------------+----------
 Fix bug in login     | high
 Deploy to production | critical

Best Practices

Based on the behavior we have explored, here are some recommendations:

Plan your enum values carefully: Think about the logical order before creating the enum type. For status values, order them by progression. For priority values, order them from lowest to highest or vice versa based on your needs.
Document the order: Add comments in your migration files explaining why enum values are ordered the way they are. This helps future developers understand the design decision.
Use descriptive names: Choose enum value names that make the ordering intuitive. For example, priority_01_low, priority_02_medium, priority_03_high makes the order explicit.
Consider alternatives for frequently changing lists: If you anticipate needing to reorder values frequently, consider using a separate lookup table with an explicit sort_order column instead of enums.
Test sorting behavior: When adding new enum values, write tests to verify that sorting works as expected across your application.
Be careful with BEFORE and AFTER: When adding new enum values, carefully consider whether you want them at the end or in a specific position relative to existing values.

Practical Example: Order Fulfillment System

Let’s put this all together with a real-world scenario. Imagine you are building an order fulfillment system:

-- Create enum for order status following the natural workflow
CREATE TYPE order_status AS ENUM (
    'placed',
    'payment_pending',
    'payment_confirmed',
    'processing',
    'shipped',
    'out_for_delivery',
    'delivered',
    'cancelled',
    'refunded'
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number TEXT NOT NULL,
    customer_name TEXT NOT NULL,
    status order_status NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (order_number, customer_name, status) VALUES
    ('ORD-001', 'Alice', 'delivered'),
    ('ORD-002', 'Bob', 'processing'),
    ('ORD-003', 'Charlie', 'shipped'),
    ('ORD-004', 'Diana', 'payment_pending'),
    ('ORD-005', 'Eve', 'out_for_delivery');

Now when you query orders sorted by status:

SELECT order_number, customer_name, status 
FROM orders 
ORDER BY status;

The results follow the natural order of fulfillment:

 order_number | customer_name |     status
--------------+---------------+------------------
 ORD-004      | Diana         | payment_pending
 ORD-002      | Bob           | processing
 ORD-003      | Charlie       | shipped
 ORD-005      | Eve           | out_for_delivery
 ORD-001      | Alice         | delivered

You can also find orders that are still in active fulfillment:

SELECT order_number, customer_name, status 
FROM orders 
WHERE status >= 'processing' AND status <= 'out_for_delivery'
ORDER BY status;

This returns:

 order_number | customer_name |     status
--------------+---------------+------------------
 ORD-002      | Bob           | processing
 ORD-003      | Charlie       | shipped
 ORD-005      | Eve           | out_for_delivery

Conclusion

PostgreSQL enum sorting behavior is based on ordinal position, which is determined by the order in which values are defined when creating the enum type. This has several important implications:

The order of enum values is permanent and cannot be changed without recreating the enum
ORDER BY clauses on enum columns sort by ordinal position, not alphabetically
Comparison operators work based on ordinal positions
New values can be added at specific positions using BEFORE or AFTER clauses
Planning the correct order upfront is crucial for long-term maintainability

Understanding this behavior helps you design better database schemas and avoid unexpected sorting results. When working with enums, always consider the logical order of values and how they will be used in queries. If you need more flexibility in ordering, consider using lookup tables with explicit sort order columns instead of enums.

Last updated on July 18, 2025

What is the difference between localhost, 127.0.0.1 and 0.0.0.0?