Today I Learned

A Hashrocket project

Nulls Last, Nulls First

Postgres query sorting has a great feature,nulls first/nulls last, that helps us specify where in a query result null values appear.

Nulls in Postgres are sorted as larger than any other value, so this is a descending sort containing null values (πŸ’€ == null):

example=# select * from users order by full_name desc;
  full_name
--------------
 πŸ’€
 πŸ’€
 πŸ’€
 Tracy Jordan
 Liz Lemon
 Jack Donaghy

Reverse the null position with nulls last:

 example=# select * from users order by full_name desc nulls last;
  full_name
--------------
 Tracy Jordan
 Liz Lemon
 Jack Donaghy
 πŸ’€
 πŸ’€
 πŸ’€
(6 rows)

Here’s the opposite query, ascending:

example=# select * from users order by full_name asc;
  full_name
--------------
 Jack Donaghy
 Liz Lemon
 Tracy Jordan
 πŸ’€
 πŸ’€
 πŸ’€
(6 rows)

Now, reverse the null position with nulls first:

example=# select * from users order by full_name asc nulls first;
  full_name
--------------
 πŸ’€
 πŸ’€
 πŸ’€
 Jack Donaghy
 Liz Lemon
 Tracy Jordan
(6 rows)

Lots of ways to explore our data.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.