Postgresql – tell postgres to use a different “collation” for an integer column

order-bypostgresql

Let's say I'm using an integer column to represent an "enum" in my application code, for a "priority" attribute.

  • 0=low
  • 1=medium
  • 2=high

The app is operating for a while like that, and then I want to add two more

  • 3=medium-low
  • 4=medium-high

Now I want to be able to index this column and do queries on it using the correct semantic ordering. Is there a way to tell postgres, when creating the index and also when doing the query, that the number ordering isn't the semantic ordering?

Best Answer

You can use a CASE expression for the ORDER BY:

order by case the_column 
            when 0 then 0 
            when 3 then 1
            when 1 then 2
            when 4 then 3
            when 2 then 4
            else 5 --- everything else
         end
Related Question