123

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 ??
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
mhd
  • 4,561
  • 10
  • 37
  • 53

2 Answers2

235

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 32
    I did. That's how I got this page :) – ibrewster Jul 22 '13 at 20:09
  • 2
    Thanks for the answer. SQLite3 added this feature and before using it, I wanted to make sure it was going to be compatible with PostgreSQL. Turns out it's a standard thing these days! – Brad Jul 08 '20 at 18:15
  • 2
    Worth noting: `NULLS FIRST` is default for `DESC`, `NULLS LAST` is default for `ASC`. – Ivan Rubinson Jun 24 '21 at 14:39
20

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;
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • 1
    Also, but you probably know this, `ASC` is the default sorting order so you do not necessarily need to type it. – mechanical_meat Mar 01 '12 at 04:29
  • 4
    `NULLS FIRST` is also the ANSI compliant way to write nulls first. –  Sep 24 '14 at 19:07
  • 3
    @a_horse_with_no_name It's useful in my case because I want to ignore the value if it's not null, and then sort by another column instead. (Im sorting notifications — unseen first (then `seenAt is null`), but if seen, then I sort by `createdAt` instaed) – KajMagnus Dec 22 '15 at 22:16
  • 3
    Agreed; I found this useful when just wanting to sort by whether or not a value was present (in this case, a date) — and then sorting by another value secondarily. `NULLS FIRST` still sorted by the actual value if the value was non-null. This did the trick for me. Thanks! – Ben Kreeger Jan 08 '16 at 17:12
  • 1
    This is the best solution. Can be used with anything else like for eg `deleted_at >= xyz time` then 0 else 1 ... – Koushik Das Jul 10 '21 at 15:31