DB enums are ordered

A lesser known fact about PG enums is that they are ordered. This can be really handy when values have an implicit ordering.

Let's imagine a record Issue(criticality: string). criticality should have the following three possible values: critical, medium, low.

Sorting with Issue.all.order(criticality: :desc) will return the following order:

1. The 'medium' issue
2. The 'low' issue
3. The 'critical' issue

This happens because the database column backing the criticality attribute is a string and PG will use a collation Show archive.org snapshot to determine the result of comparisons. In most collations, the following SQL statements explain the sorting above:

CopySELECT 'medium' > 'low'; // true
SELECT 'medium' > 'critical'; // true

SELECT 'low' > 'medium'; // false
SELECT 'low' > 'critical'; // true

SELECT 'critical' > 'low'; // false
SELECT 'critical' > 'medium'; // false

Using an enum

Let's change the type of the Issue to an actual enum with the following definition:

CopyCREATE TYPE criticality AS ENUM ('low', 'medium', 'critical');

Sorting with Issue.all.order(criticality: :desc) will now return the following order:

1. The 'critical' issue
2. The 'medium' issue
3. The 'low' isse

PG Enums are ordered Show archive.org snapshot based on their specified order when creating them, in this case ('low', 'medium', 'critical').

When adding a new value, you can choose its position:

CopyALTER TYPE criticality ADD VALUE 'high' AFTER 'medium';