Sort by column ASC, but NULL values first?
Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:
... ORDER BY last_updated NULLS FIRSTThe typical use case is with descending sort order (DESC), which produces the complete inversion of the default ascending order (ASC) with null values first - which is often not desirable. To sort NULL values last:
... ORDER BY last_updated DESC NULLS LASTTo support the query with an index, make it match:
CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);Postgres can read btree indexes backwards, but for some query plans it matters where NULL values are appended. See:
You can create a custom ORDER BY using a CASE statement.
The CASE statement checks for your condition and assigns to rows which meet that condition a lower value than that which is assigned to rows which do not meet the condition.
It's probably easiest to understand given an example:
SELECT last_updated FROM your_table ORDER BY CASE WHEN last_updated IS NULL THEN 0 ELSE 1 END, last_updated ASC;