Sort by column ASC, but NULL values first?

Question

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

Accepted Answer

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:

- Performance impact of view on aggregate function vs result set limiting

Sort by column ASC, but NULL values first?

Solution

Problem