How to Order PostgreSQL Data with NULLs Last, Except for a Specific Value?

Ordering NULL Values After All Others, Except for Special

When sorting data in a PostgreSQL table that contains optional ordering fields, a common challenge is handling null values. It is desirable to place tasks with null sort values after all others, but grant precedence to tasks with a special sort value, such as -1.

This can be achieved by utilizing the COALESCE function in conjunction with boolean operators, specifically the (IS NOT DISTINCT FROM) operator. The following query demonstrates this approach:

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

How it Works:

The (sort IS NOT DISTINCT FROM -1) expression evaluates to FALSE for all values except -1, which evaluates to TRUE. In PostgreSQL's default sort order, NULL values are placed last, while TRUE is ranked higher than FALSE.

By incorporating this expression into the ORDER BY clause, tasks with sort values of -1 are positioned after tasks with non-null sort values, while tasks with null sort values are placed after all other tasks.

Additional Note:

An alternative equivalent query can be written using the DESC keyword:

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

The above is the detailed content of How to Order PostgreSQL Data with NULLs Last, Except for a Specific Value?. For more information, please follow other related articles on the PHP Chinese website!