🔗 Applying natural sort to a Django CharField with PostgreSQL

When sorting strings, unexpected results can occur if numeric parts are involved, as typical databases use lexicographical ordering. For example, in a Django model where chapter titles are stored as strings, sorting titles like "Chapter 1", "Chapter 10", and "Chapter 2" alphabetically leads to an illogical order.

To achieve a natural sort in PostgreSQL, a custom collation must be created using the CREATE COLLATION statement. The article details how to create a natural sort collation with ICU support and how to implement it in a Django migration. It showcases the Collate() function for ordering querysets as well as a method to apply the collation directly to the CharField. This setup allows Django to naturally sort chapters by title, yielding more intuitive results when dealing with numeric strings.