How Does PostgreSQL Handle NULL Values in ORDER BY Clauses?

Ordering NULL Values in PostgreSQL

Sorting rows with NULL values can be customized in PostgreSQL. By default, NULL values are sorted last in ascending order. However, in descending order, NULL values appear first.

Sorting NULL Values Last in Ascending Order

By default, NULL values are sorted last in ascending order. This means that rows with NULL values in specific fields will appear at the bottom of the table when sorted by those fields.

Sorting NULL Values First in Descending Order

To sort NULL values first in descending order, PostgreSQL 8.3 and later versions offer the NULLS LAST option:

ORDER BY somevalue DESC NULLS LAST

Sorting NULL Values First in Descending Order for PostgreSQL 8.2 and Earlier

For PostgreSQL 8.2 and earlier versions or other RDBMS without the NULLS LAST feature, you can use the following workaround:

ORDER BY (somevalue IS NULL), somevalue DESC

Since FALSE sorts before TRUE, NULL values (which are represented as NULL in PostgreSQL) will be considered FALSE and therefore sorted last, effectively pushing them to the top of the sorted table in descending order.

References

• [Sort by column ASC, but NULL values first?](https://stackoverflow.com/questions/5259380/sort-by-column-asc-but-null-values-first)
• [The manual on SELECT](https://www.postgresql.org/docs/current/static/sql-select.html)

The above is the detailed content of How Does PostgreSQL Handle NULL Values in ORDER BY Clauses?. For more information, please follow other related articles on the PHP Chinese website!