PostgreSQL 9.6如何批量获取分区表与非分区表名称

嘿，针对你在PostgreSQL 9.6里想要分别获取所有非分区表和分区表名称的需求，我来给你梳理下可行的方案——毕竟你已经知道怎么查询指定表的子分区了，咱们把这个逻辑扩展一下就行～

在PostgreSQL 9.6里，分区是基于继承机制实现的（声明式分区是PostgreSQL 10才引入的），所以所有的分区表都会作为子表出现在pg_inherits系统视图里。你可以用下面的查询来获取指定schema下的所有分区表：

SELECT DISTINCT i.inhrelid::regclass AS partition_table
FROM pg_inherits i
JOIN pg_class c ON i.inhrelid = c.oid
JOIN pg_namespace ns ON c.relnamespace = ns.oid
WHERE ns.nspname = 'public' -- 替换成你要查询的schema，去掉该行则查询所有schema
AND c.relkind = 'r'; -- 仅筛选普通表，排除视图、序列等对象

小细节说明： #

• DISTINCT用来避免重复结果（虽然大多数分区表只会继承一个父表，但保险起见加上）
• regclass类型会自动带上schema名称，让表名更清晰
• 如果需要查询所有schema的分区表，直接删掉WHERE ns.nspname = 'public'这一行即可

非分区表就是那些没有作为子表参与继承的普通表，这里需要注意：分区的父表（比如你例子里的public.documento）本身不是分区，所以默认会被包含进来。如果需要包含父表，用下面的查询：

SELECT c.relname AS non_partition_table
FROM pg_class c
JOIN pg_namespace ns ON c.relnamespace = ns.oid
WHERE ns.nspname = 'public'
AND c.relkind = 'r'
AND c.oid NOT IN (
    SELECT DISTINCT inhrelid FROM pg_inherits
);

如果需要排除分区父表（只查完全无关的普通表）： #

如果你想把那些作为分区模板的父表也排除掉，可以再添加一个条件，排除pg_inherits里的父表OID：

SELECT c.relname AS non_partition_table
FROM pg_class c
JOIN pg_namespace ns ON c.relnamespace = ns.oid
WHERE ns.nspname = 'public'
AND c.relkind = 'r'
AND c.oid NOT IN (
    SELECT DISTINCT inhrelid FROM pg_inherits
)
AND c.oid NOT IN (
    SELECT DISTINCT inhparent FROM pg_inherits
);

如果你想一次性查看所有分区父表对应的子分区，可以用这个查询，比单个查询更高效：

SELECT 
    i.inhparent::regclass AS parent_table, 
    i.inhrelid::regclass AS partition_table
FROM pg_inherits i
JOIN pg_class pc ON i.inhparent = pc.oid
JOIN pg_namespace pns ON pc.relnamespace = pns.oid
JOIN pg_class cc ON i.inhrelid = cc.oid
JOIN pg_namespace cns ON cc.relnamespace = cns.oid
WHERE pns.nspname = 'public'
AND cns.nspname = 'public'
AND pc.relkind = 'r'
AND cc.relkind = 'r';

这样就能清晰看到每个父表对应的所有分区表了。

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