How to Sort Nulls Last in PostgreSQL with a Specific Value Exception?

Sorting: Nulls After All but Special Exception

When organizing data in a database table with an optional sorting field, it's common to place null values last. However, sometimes an exception is needed for specific values. In PostgreSQL, a requirement arose to sort null values after non-nulls, with -1 being an exception that should come after all others.

To achieve this, a straightforward solution presented itself:

SELECT *
FROM   tasks
ORDER  BY (sort IS NOT DISTINCT FROM -1), sort;

Postgres treats boolean values as a type, including NULL. Its default sorting order is:

• FALSE (0)
• TRUE (1)
• NULL

In the expression (sort IS NOT DISTINCT FROM -1), the condition evaluates to FALSE for all values except -1, which evaluates to TRUE and sorts last. Combining this with a secondary ordering by sort orders the data as desired.

An alternative query that produces the same result is:

SELECT *
FROM   tasks
ORDER  BY (sort IS DISTINCT FROM -1) DESC, sort;

By utilizing Postgres's boolean functionality and combining it with the ORDER BY clause, it becomes possible to handle complex sorting requirements with ease.

The above is the detailed content of How to Sort Nulls Last in PostgreSQL with a Specific Value Exception?. For more information, please follow other related articles on the PHP Chinese website!