无分组表查询最新日期行 不使用WHERE子句适配物化视图REFRESH CONCURRENTLY

以下两种方案都完全不使用WHERE子句，返回结果和你原有SQL逻辑完全一致，同时满足PostgreSQL物化视图REFRESH CONCURRENTLY的语法要求：

方案1：内连接关联最大日期结果（兼容所有PostgreSQL版本） #

将过滤逻辑迁移到JOIN的ON子句中，不需要额外写WHERE过滤：

SELECT t1.*
FROM table1 t1
INNER JOIN (
    SELECT MAX(date1) AS max_date FROM table1
) t_max ON t1.date1 = t_max.max_date

方案2：窗口函数+QUALIFY子句（PostgreSQL 13及以上版本支持） #

使用专门用于窗口函数结果过滤的QUALIFY语法，写法更简洁：

SELECT *
FROM table1
QUALIFY date1 = MAX(date1) OVER ()

要让物化视图支持REFRESH CONCURRENTLY，需要额外完成配置：

• 基于上述语句创建物化视图
• 为物化视图创建覆盖唯一标识字段的唯一索引，示例如下：

-- 创建物化视图（以方案1为例）
CREATE MATERIALIZED VIEW mv_latest_table1 AS
SELECT t1.*
FROM table1 t1
INNER JOIN (
    SELECT MAX(date1) AS max_date FROM table1
) t_max ON t1.date1 = t_max.max_date;

-- 创建唯一索引，示例使用table1的主键id作为唯一标识
CREATE UNIQUE INDEX idx_mv_latest_table1_pkey ON mv_latest_table1(id);

配置完成后即可执行REFRESH CONCURRENTLY mv_latest_table1;完成并发刷新。

内容的提问来源于stack exchange，提问作者Sam