Search code examples
sqlpostgresqlnullsql-order-by

Sort by column ASC, but NULL values first?

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 ??
Solution

  • 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: