How to Order PostgreSQL Records by Date with NULL Values First?

PostgreSQL database records are sorted by date, null values ​​first

In PostgreSQL, it is often necessary to sort query results in ascending order by a date/time field (e.g. last_updated) while ensuring that null value records come first.

PostgreSQL provides the ORDER BY modifier for NULLS FIRST | LAST expressions to achieve this purpose. By default, NULLS FIRST is used in descending order (DESC), causing null values ​​to be sorted last. To sort NULL values ​​first in ascending order (ASC), you can use the following syntax:

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

In order for the index to support this query, the index should match the sort order:

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

PostgreSQL can handle inverted indexes efficiently, making it look like the following:

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

However, for some query plans, the position of NULL values ​​can affect performance. For more information on this topic, please see the following:

• About the impact of aggregate function views on performance and result set limitations

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