Custom ORDER BY Explanation

Each expression gets evaluated as a bool and treated as 0 for false and 1 for true and sorted appropriately. Even though this works, the logic is hard to follow (and thus maintain). What I use is a function that finds a value's index in an array.

ORDER BY idx(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)

This is much easier to follow. Nails will be sorted first and nuts sorted last. You can see how to create the idx function in the Postgres snippets repository. http://wiki.postgresql.org/wiki/Array_Index

@Scott Bailey suggested great idea. But it can be even simpler (you don't have to create custom function) since PostgreSQL 9.5. Just use array_position function:

ORDER BY array_position(array['Nails','Bolts','Washers','Screws','Staples','Nuts'], s.type)

I've never seen it but it seems to make sense.

At first it orders by s.type != 'Nails'. This is false for every row that contains Nails in the type column. After that it is sorted by Bolts. Again for all columns that do contain Bolts as a type this evaluates to false. And so on.

A small test reveals that false is ordered before true. So you have the following: First you get all rows with Nails on top because the according ORDER BY evaluated to false and false comes first. The remaining rows are sorted by the second ORDER BY criterion. And so on.

 type     | != Nails | != Bolts | != Washers
'Nails'   | false    | true     | true
'Bolts'   | true     | false    | true
'Washers' | true     | true     | false

with array_position, it needs to have the same type that you're querying against.

e.g:

select array_position(array['foo'::char,'bar','baz'::char], 'bar');
select array_position(array['foo'::char,'bar','baz'::char], 'baz'::char);