Every developer knows the pain of sorting database rows by some custom field. The easiest Example of this are DNS Records.

Today i’ve come across a solution using array_position. Basically you can pass it the Order of elements you want on top, and the rest will be below.

It works like this

SELECT hostname, type, contentn FROM records AS r
ORDER BY type, array_position(array["SOA"::varchar, NS"::varchar], r.type)

Now all records having type SOA will be first, type NS second and type A third. Everything else will come after.

The important part

Make sure you typecast your custom array into the type of the column you are ordering against. In my case the type column is a varchar, that’s why i am casting all elements to it.

I hope this helps some of you avoiding to write custom sorting in code.