如何从PostgreSQL系统目录获取范围分区的起止值等详细信息

问题背景 #

需要列出PostgreSQL中所有范围分区的完整信息，包括子分区名、父表名、分区起始值和结束值。已尝试以下查询：

SELECT c.relname as partition_list,p.relname as parent_tbl FROM pg_inherits i JOIN pg_class p ON i.inhparent = p.oid
JOIN pg_class c ON i.inhrelid = c.oid WHERE  p.relkind IN ('r', 'p');

当前输出结果 #

"testpartpartition_1"    "parentpartiontbl"
"testpartpartition_2"    "parentpartiontbl"

分区创建语句示例 #

CREATE TABLE testpartpartition_1 PARTITION OF parentpartiontbl FOR VALUES FROM (1) TO (5)
CREATE TABLE testpartpartition_2 PARTITION OF parentpartiontbl FOR VALUES FROM (6) TO (10)

期望输出格式 #

child_partition            parent_tbl           min_rangeval      max_rangeval
---------------------------------------------------------------------------------
"testpartpartition_1"    "parentpartiontbl"         1                  5
"testpartpartition_2"    "parentpartiontbl"         6                  10

解决方案 #

使用以下查询可以获取包含分区边界值的完整信息：

SELECT
    c.relname AS child_partition,
    p.relname AS parent_tbl,
    (regexp_match(pg_get_expr(c.relpartbound, c.oid), 'FROM \((.*?)\) TO'))[1] AS min_rangeval,
    (regexp_match(pg_get_expr(c.relpartbound, c.oid), 'TO \((.*?)\)'))[1] AS max_rangeval
FROM pg_inherits i
JOIN pg_class p ON i.inhparent = p.oid
JOIN pg_class c ON i.inhrelid = c.oid
WHERE p.relkind IN ('r', 'p')
AND c.relispartition = TRUE;

说明 #

• pg_get_expr(c.relpartbound, c.oid)：将存储在pg_class.relpartbound中的分区边界表达式转换为可读的SQL语句。
• 正则表达式regexp_match：从生成的表达式中提取起始值（FROM子句中的内容）和结束值（TO子句中的内容）。
• 条件c.relispartition = TRUE：确保只返回分区表，避免误匹配其他继承表。

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