PostgreSQL Order By Enum

In this article, I’ll explain everything you need to know about ordering by enum values in PostgreSQL, providing real-time examples and best practices I’ve developed while working with some of my prestigious clients.

PostgreSQL Order By Enum

When you create an enum type in PostgreSQL, it automatically establishes an order based on the sequence in which you define the values.

What Are PostgreSQL ENUM Types?

Before we discuss ordering, let’s clarify what ENUM types are. In PostgreSQL, an ENUM is a custom data type consisting of a static, ordered set of values. Let us see this with an example. Let us execute the below query to create the Enum. This creates a new type called productorder_status with five possible values.

CREATE TYPE productorder_status AS ENUM (
    'pending', 
    'processing', 
    'shipped', 
    'delivered', 
    'canceled'
);

After executing the above query, I got the expected output, as shown in the screenshot below.

Now, let’s explore how to order data based on these enum values.

Method 1: Using the Default ENUM

Let us create a table of ProductOrders using the query below.

CREATE TABLE ProductOrders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    status productorder_status,
    order_date DATE
);

After executing the above query, the table was created successfully.

Now, let us use the below query to insert values into the table created above.

INSERT INTO ProductOrders (customer_name, status, order_date) VALUES
('John Smith', 'delivered', '2025-01-15'),
('Sarah Johnson', 'pending', '2025-02-20'),
('Mike Thompson', 'shipped', '2025-02-01'),
('Lisa Williams', 'canceled', '2025-01-10'),
('Robert Davis', 'processing', '2025-02-15');

After executing the above query, I got the expected output, as shown in the screenshot below.

Now, let us retrieve orders sorted by their status enum order using the below query.

SELECT order_id, customer_name, status, order_date 
FROM ProductOrders 
ORDER BY status;

After executing the above query, I got the expected output, as shown in the below screenshot.

Method 2: Ordering with CASE Expressions

Sometimes, the default enum order doesn’t match your business requirements. Use the query below to implement a custom order.

SELECT order_id, customer_name, status, order_date 
FROM ProductOrders 
ORDER BY 
    CASE status
        WHEN 'pending' THEN 1
        WHEN 'processing' THEN 2
        WHEN 'shipped' THEN 3
        WHEN 'delivered' THEN 4
        WHEN 'canceled' THEN 5
    END;

This approach gives you complete control over the sorting order, independent of how the enum was initially defined.

After executing the above query, I got the expected output, as shown in the below screenshot.

Method 3: Combining ENUM Ordering with Other Fields

In real-world scenarios, you’ll often need to sort by multiple criteria. For example, you might want to sort orders by status first and then by date within each status. This will group orders by their status (following the enum order), then sort by date in descending order within each status group. Let us execute the query below.

SELECT order_id, customer_name, status, order_date 
FROM ProductOrders 
ORDER BY status, order_date DESC;

After executing the above query, I got the expected output, as shown in the below screenshot.

Best Practices for Working with ENUM Ordering

Based on my experience implementing database solutions for companies like TechNorth in Chicago and DataStream in Austin, I’ve developed these best practices:

1. When creating an enum, consider possible future values and where they would fit in the order hierarchy.
2. While convenient, they’re less flexible than lookup tables when requirements change.
3. Always verify that sorting works as expected, especially when combining enum ordering with other fields.
4. Consider using explicit CASE statements for code readability even when the default order works for your needs.

Conclusion

Knowing how to use ORDER BY with enum types in PostgreSQL provides a great way to implement business-specific sorting logic in your database. By following the methods and best practices mentioned in this article, you can learn how enum ordering works by default and methods to customize it when needed.

You may also like the articles below.

• PostgreSQL ORDER BY COUNT
• PostgreSQL Order By Union
• PostgreSQL Order By Array
• PostgreSQL Order By Group By
• PostgreSQL Order By JSON Field