Postgres: ORDER BY custom text order
Found this scenario where I wanted to (out of pure laziness) sort records by specific order of a column’s possible values. I looked around and to my surprise I found a way to do it.
- I would like to list the in-stock products first.
- I have a products table with an
- The valid values for
availabilitycolumn are ‘instock’ and ‘outofstock’.
SELECT * products ORDER BY array_position( array['instock','outofstock'], products.availability )
The above SQL uses the
array_position function. It requires two arguments.
- A list of sorted values
- The column that we want to sort.
If you are using #RubyOnRails, that would be the following:
# First turn your custom SQL into an Arel SQL literal order_sql = Arel.sql("array_position(array['instock','outofstock'], products.availability)") # Pass the Arel literal to the order method products = Product.order(order_sql)
What if the column has
What I have is scraped data. I found out that there are times when the
If I just wanted NULL values first or last, I would use
NULLS FIRST or
NULLS LAST. The SQL would be something like below.
SELECT * products ORDER BY array_position(array['instock','outofstock'], products.availability) NULLS FIRST
But for products whose availability I am not sure of (
NULL value), I want them to be listed on top of products that are out of stock. So I now include the NULL value as the list of values to order by.
SELECT * products ORDER BY array_position(array['instock',NULL,'outofstock'], products.availability)
An easier way
If the values for a column are going to a finite list of values, this column would benefit from storing the availability as integers, along with using
NULLS LAST or
I’ve been toying with #Postgres for a hobby project and I’m learning quite a lot of nice things. If this is something you are interested in - follow me on @HashNuke