Can I tell postgres to use a different "collation" for an integer column? 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?
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