How to Sort NULL Values in PostgreSQL: First or Last?

Ordering NULL Values in PostgreSQL Tables

Sorting rows with NULL values to the end of a table can be a common requirement in data management. PostgreSQL provides multiple ways to handle NULL values in sorting, depending on whether you want them to appear first or last.

Sorting NULL Values to the Top (Descending Order)

By default, NULL values are sorted last in ascending order. To sort them in descending order and have them appear on top, PostgreSQL versions 8.3 and higher offer the NULLS LAST clause.

ORDER BY somevalue DESC NULLS LAST

Sorting NULL Values to the End (Ascending Order)

For PostgreSQL versions prior to 8.3 and other RDBMS without the NULLS LAST feature, you can use the following workaround:

ORDER BY (somevalue IS NULL), somevalue DESC

This expression takes advantage of the fact that FALSE (representing NULL values) sorts before TRUE. Therefore, NULL values are placed at the end of the sorted results.

Standard SQL NULL Ordering

The SQL standard dictates that NULL values should be sorted last in ascending order and first in descending order. However, some RDBMS may not adhere to this standard. PostgreSQL follows the standard by default, but provides the NULLS LAST clause to explicitly control the sorting behavior of NULL values in descending order.

The above is the detailed content of How to Sort NULL Values in PostgreSQL: First or Last?. For more information, please follow other related articles on the PHP Chinese website!