I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated.
But that field is allowed to be empty or null and I want records with null in last_updated come before non-null last_updated.
Is this possible?
order by last_updated asc -- and null last_updated records first ??
2 Answers 2
Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:
... ORDER BY last_updated NULLS FIRST
The 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 LAST
To 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, so that's effectively almost the same as just:
CREATE INDEX foo_idx ON tbl (last_updated);
For some query plans it matters where NULL values are appended. See:
1 Comment
NULLS FIRST is default for DESC, NULLS LAST is default for ASC.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;
5 Comments
ASC is the default sorting order so you do not necessarily need to type it.seenAt is null), but if seen, then I sort by createdAt instaed)NULLS FIRST still sorted by the actual value if the value was non-null. This did the trick for me. Thanks!deleted_at >= xyz time then 0 else 1 ...