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.