分区表相关数据字典和视图

pg_partition

pg_partition_rule

pg_class

pg_partition_columns 系统视图被用来显示一个分区表的分区键列；

--列出所有分区表对应的分区键信息
select * from pg_partition_columns;

pg_partitions 系统视图被用于显示分区表的结构；

--列出所有分区表对应的子表的约束条件和分区名和分区类型等信息
select * from pg_partitions;

分区表常用查询

把所有的业务表列出来（包括分区表和非分区表，但是不要子表出现）

select
  n.nspname schemaname,
  c.relname tablename
from
  pg_class c left join pg_namespace n on n.oid = c.relnamespace
left join pg_partition_rule r on c.oid = r.parchildrelid
where c.relkind='r'
and c.relstorage in ('a','h','c')
and r.parchildrelid is null --去除子表
and n.nspname <> 'pg_catalog'
and n.nspname <> 'information_schema'
and n.nspname <> 'pg_toast' and n.nspname <> 'dba'
and not n.nspname like 'pg_temp%';

找出所有有分区的业务表

--方法一
 select  parrelid::regclass from pg_partition; --只会存储父表（该表存在分区表）
--方法二
 select
 n.nspname schemaname,
 c.relname tablename
 from
   pg_class c left join pg_namespace n on n.oid = c.relnamespace
   inner join pg_partition p on p.parrelid = c.oid
   where c.relkind='r'
   and c.relstorage in ('a','h','c')
   and n.nspname <> 'pg_catalog'
   and n.nspname <> 'information_schema'
   and n.nspname <> 'pg_toast' and n.nspname <> 'dba'
   and not n.nspname like 'pg_temp%';

找出所有的子表

--方法一
 select parchildrelid::regclass from pg_partition_rule; --只会存储子表
 select p.parrelid::regclass,parchildrelid::regclass from pg_partition p left join pg_partition_rule r1 on p.oid=r1.paroid;
--方法二
select
n.nspname schemaname,
c.relname tablename
from
  pg_class c left join pg_namespace n on n.oid = c.relnamespace
  inner join pg_partition_rule p on p.parchildrelid = c.oid
  where c.relkind='r'
  and c.relstorage in ('a','h','c')
  and n.nspname <> 'pg_catalog'
  and n.nspname <> 'information_schema'
  and n.nspname <> 'pg_toast' and n.nspname <> 'dba'
  and not n.nspname like 'pg_temp%';

找出所有有分区的表及其包含的子表名

select
n.nspname schemaname,
c.relname tablename
from
  pg_class c left join pg_namespace n on n.oid = c.relnamespace
  inner join pg_partition p on p.parrelid = c.oid
  UNION
select
n.nspname schemaname,
c.relname tablename
from
  pg_class c left join pg_namespace n on n.oid = c.relnamespace
inner join pg_partition_rule q on q.parchildrelid = c.oid
  where c.relkind='r'
  and c.relstorage in ('a','h','c')
  and n.nspname <> 'pg_catalog'
  and n.nspname <> 'information_schema'
  and n.nspname <> 'pg_toast' and n.nspname <> 'dba'
  and not n.nspname like 'pg_temp%';