Josh McArthur

Arbitrary Ordering in PostgreSQL when Rails + ENUM = No.

14 Jul 2011

I’ve recently had to do a custom sort for a work project that has required a sort on something that is not naturally sortable correctly (For example, alphabetical or numerical sorting). While searching for a completely different solution, I came across this post that outlined a nice technique. Basically, when you have a field in your Rails model with a predefined set of possible values, you can use a CASE statement in PostgreSQL to perform the sort in whichever order these values should appear. Here’s a sample of how this could be achieved using Rails:

Result.order("CASE " + "WHEN medal='gold'
THEN 1 " + "WHEN medal='silver' THEN 2 " + "WHEN medal='bronze' THEN 3 " +
"ELSE 4 " + + "END,name")

It’s messy of course - how you want to format the SQL string is up to you, but it’s a great solution when you don’t have the normal sorting capabilities of a ENUM datatype available to you.