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:

<code class="language-sql">... ORDER BY last_updated NULLS FIRST</code>

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:

<code class="language-sql">... ORDER BY last_updated DESC NULLS LAST</code>

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

<code class="language-sql">CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);</code>

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

<code class="language-sql">CREATE INDEX foo_idx ON tbl (last_updated);</code>

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!