How to Sort by Ascending Date/Time with NULL Values First in PostgreSQL?

PostgreSQL table date/time fields sorted in ascending order, NULL values ​​first

When sorting by date/time field in ascending order in a PostgreSQL table, NULL values ​​need to be given priority. Additionally, non-null last_updated records should be sorted in ascending order immediately after NULL values.

PostgreSQL's ORDER BY expression provides the NULLS FIRST | LAST modifier to handle this situation. By specifying NULLS FIRST, records with a NULL value for the last_updated field will appear before non-NULL records:

... ORDER BY last_updated NULLS FIRST

However, using NULLS FIRST with descending sort (DESC) returns the opposite result, NULL values ​​will appear last. To put NULL values ​​last in descending order, use NULLS LAST:

... ORDER BY last_updated DESC NULLS LAST

To use an index with the modified sort order, it should match the index definition:

CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);

Although PostgreSQL can read btree indexes in reverse, it is more efficient to create an index that matches the sort order:

CREATE INDEX foo_idx ON tbl (last_updated);

The position of NULL values ​​in the appended list affects query performance. For more information, see Performance Impact of Views on Aggregate Functions and Result Set Limitations.

The above is the detailed content of How to Sort by Ascending Date/Time with NULL Values First in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!