PostgreSQL如何分别查询普通表、分区父表及分区的名称

需求1：查询普通表和分区父表名称 #

可以通过PostgreSQL系统表pg_class的字段特征筛选，匹配逻辑为：排除分区子表、仅保留普通表和分区父表，对应SQL如下：

SELECT relname AS table_name
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
WHERE n.nspname = 'public'
  AND relkind IN ('r', 'p')
  AND relispartition = false
ORDER BY relname;

查询结果返回company、industry、customers三个表名。

需求2：查询所有分区子表名称 #

筛选逻辑为：仅标记为分区的普通表实体，对应SQL如下：

SELECT relname AS partition_name
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
WHERE n.nspname = 'public'
  AND relkind = 'r'
  AND relispartition = true
ORDER BY partition_name;

查询结果返回cust_arr_small、cust_arr_medium、cust_arr_large三个分区名。

psycopg2 调用示例 #

可直接在Python脚本中使用的完整调用代码：

import psycopg2
from psycopg2.extras import RealDictCursor

# 按需修改数据库连接参数
conn_params = {
    "dbname": "你的数据库名",
    "user": "admin",
    "password": "你的数据库密码",
    "host": "你的数据库主机地址",
    "port": 5432
}

if __name__ == "__main__":
    conn = psycopg2.connect(**conn_params)
    # 获取普通表+分区父表列表
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        cur.execute("""
            SELECT relname AS table_name
            FROM pg_catalog.pg_class
            JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
            WHERE n.nspname = 'public'
              AND relkind IN ('r', 'p')
              AND relispartition = false
            ORDER BY relname;
        """)
        normal_parent_tables = [row["table_name"] for row in cur.fetchall()]
        # 后续可直接使用normal_parent_tables变量做业务处理

    # 获取分区子表列表
    with conn.cursor(cursor_factory=RealDictCursor) as cur:
        cur.execute("""
            SELECT relname AS partition_name
            FROM pg_catalog.pg_class
            JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
            WHERE n.nspname = 'public'
              AND relkind = 'r'
              AND relispartition = true
            ORDER BY partition_name;
        """)
        partition_tables = [row["partition_name"] for row in cur.fetchall()]
        # 后续可直接使用partition_tables变量做业务处理
    
    conn.close()

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