按列 ASC 排序，但首先是 NULL 值？

我需要按照日期/时间字段对 PostgreSQL 表进行升序排序，例如 last_updated。

但是这个字段允许为空或者为空，我希望记录在 last_updated中为空，在 之前中为非空 last_updated。
这可能吗？

order by last_updated asc  -- and null last_updated records first ??
62146 次浏览
小开

You can create a custom ORDER BY using a CASE statement.
The CASE statement checks for your condition and assigns to rows which meet that condition a lower value than that which is assigned to rows which do not meet the condition.
It's probably easiest to understand given an example:

  SELECT last_updated
FROM your_table
ORDER BY CASE WHEN last_updated IS NULL THEN 0 ELSE 1 END,
last_updated ASC;
小开
最佳答案

Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:

... ORDER BY last_updated NULLS FIRST

The typical use case is with descending sort order (DESC), which produces the complete inversion of the default ascending order (ASC) with null values first - which is often not desirable. To sort NULL values last:

... ORDER BY last_updated DESC NULLS LAST

To support the query with an index, make it match:

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

Postgres can read btree indexes backwards, but for some query plans it matters where NULL values are appended. See:


提交答案