PostgreSQL分区表：如何简化检查当日分区的视图代码？

针对你需要检查dwh.message_event分区表当日分区是否存在的需求，这里提供两种更简洁的视图实现方式：

方法1：使用pg_partition_tree（PostgreSQL 12+推荐） #

PostgreSQL 12及以上版本新增了pg_partition_tree系统视图，可直接获取分区的边界信息，无需手动处理继承关系和复杂正则：

CREATE OR REPLACE VIEW dwh.v_partitions AS
SELECT
    relid::regclass AS partition_name,
    (string_to_array(split_part(partbound, ' TO ', 1), '('))[2]::int AS date_from,
    (string_to_array(split_part(partbound, ' TO ', 2), ')'))[1]::int AS date_to
FROM pg_partition_tree
WHERE parentrelid = 'dwh.message_event'::regclass;

方法2：优化正则提取（兼容旧版本） #

如果你的PostgreSQL版本低于12，可以直接通过带捕获组的正则一次性提取起止日期，省去子查询和聚合操作：

CREATE OR REPLACE VIEW dwh.v_partitions AS
SELECT
    pt.oid::regclass AS partition_name,
    (regexp_matches(pg_get_expr(pt.relpartbound, pt.oid, true), '(\d{8}) TO (\d{8})'))[1]::int AS date_from,
    (regexp_matches(pg_get_expr(pt.relpartbound, pt.oid, true), '(\d{8}) TO (\d{8})'))[2]::int AS date_to
FROM pg_class pt
JOIN pg_inherits i ON pt.oid = i.inhrelid
WHERE i.inhparent = 'dwh.message_event'::regclass;

补充说明 #

• 方法1的pg_partition_tree返回的partbound字段格式类似RANGE (20240201) TO (20240210)，通过字符串拆分即可快速提取数值，代码更易维护。
• 方法2通过正则捕获组直接定位两个8位日期数字，避免了原查询中array_agg分组的冗余步骤。

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