Why do NULL values come first when ordering DESC i

Question

When would you ever want NULLS first when ordering a query descending or ascending?

In my opinion, the vast majority of the time the desired behavior whether sorting ascending or descending would be NULLS LAST. Instead, we should have to specify NULLS FIRST.

Answer 1

The simple answer is because that's how the people who wrote Postgres designed it. To quote:

The null value sorts higher than any other value. In other words, with ascending sort order, null values sort at the end, and with descending sort order, null values sort at the beginning.

This assumes that you have specified an ORDER BY clause, if you haven't then the rows are returned randomly.

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Answer 2

Actually, with default sort order (ASCENDING) NULL values come last.

Logic dictates that the sort order be reversed with the DESCENDING keyword, so NULLs come first in this case.

But the best part comes last: you can choose which way you want it:

• Use the NULLS FIRST | LAST clause.

Quoting the current manual, version 9.3 as of writing:

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls). When USING is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.

Bold emphasis mine.