How to Sort PostgreSQL Tables with NULL Values First or Last?

PostgreSQL table sorting: NULL value priority setting

When sorting a PostgreSQL table by a date/time field in ascending order, you may encounter a situation where some records have NULL values ​​in that field. Typically, these records appear after non-NULL records. However, you may want to display records with NULL values ​​first.

PostgreSQL's ORDER BY expression provides the NULLS FIRST modifier for this purpose. Using this modifier you can sort the table as follows:

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

This will ensure that last_updated records with NULL values ​​for fields appear before records with non-NULL values.

For descending sorting, you can use the NULLS LAST modifier:

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

To create an index that supports this query, make sure it matches the order of the ORDER BY clauses:

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

Alternatively, you can create an index without explicitly specifying the NULLS LAST option, since PostgreSQL can read btree indexes in reverse:

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

In some cases, the position of NULL values ​​in the index can affect query performance. See the PostgreSQL documentation for more information on this.

The above is the detailed content of How to Sort PostgreSQL Tables with NULL Values First or Last?. For more information, please follow other related articles on the PHP Chinese website!