获取有关表分区的信息

Question

>我有一个使用范围的主表之外的分区表。

CREATE TABLE public.partition1 PARTITION OF public.maintable 
FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2050-01-01 00:00:00')

如何使用查询来发布值范围信息。

我使用了一个查询，至少为我提供了主表和分区表的信息，但我似乎找不到访问值范围的方法从 ('2017-01-01 00：00：00'( 到 ('2050-01-01 00：00：00'(分配给表分区 1

用于获取分区表信息的查询

WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS 
(
(SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'completedorders' AND
relkind = 'p')
UNION ALL
(SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition = true)
)       
SELECT * FROM partition_info;

Answer 1

以下查询还提供有关分区的信息。从那里开始，它只是字符串操作以获取更多信息。注意：您必须更改查询中表的名称。

with recursive inh as (
select i.inhrelid, null::text as parent
from pg_catalog.pg_inherits i
join pg_catalog.pg_class cl on i.inhparent = cl.oid
join pg_catalog.pg_namespace nsp on cl.relnamespace = nsp.oid
where nsp.nspname = 'public'          ---<< change table schema here
and cl.relname = 'tablename'   ---<< change table name here
union all
select i.inhrelid, (i.inhparent::regclass)::text
from inh
join pg_catalog.pg_inherits i on (inh.inhrelid = i.inhparent)
)
select c.relname as partition_name,
n.nspname as partition_schema,
pg_get_expr(c.relpartbound, c.oid, true) as partition_expression,
pg_get_expr(p.partexprs, c.oid, true) as sub_partition,
parent,
case p.partstrat
when 'l' then 'LIST'
when 'r' then 'RANGE'
end as sub_partition_strategy
from inh
join pg_catalog.pg_class c on inh.inhrelid = c.oid
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left join pg_partitioned_table p on p.partrelid = c.oid
order by n.nspname, c.relname

Answer 2

具有parent_schema且唯一的表相关的版本：

with recursive inh as (
select i.inhrelid, cl.relname as parent,nsp.nspname as  parent_schema
from pg_catalog.pg_inherits i
join pg_catalog.pg_class cl on i.inhparent = cl.oid
join pg_catalog.pg_namespace nsp on cl.relnamespace = nsp.oid
where nsp.nspname = 'public'          ---<< change table schema here
and cl.relname = 'tablename'   ---<< change table name here
and cl.relkind='p'
union all
select i.inhrelid, (i.inhparent::regclass)::text,inh.parent_schema
from inh
join pg_catalog.pg_inherits i on (inh.inhrelid = i.inhparent)
)
select c.relname as partition_name,
n.nspname as partition_schema,
pg_get_expr(c.relpartbound, c.oid, true) as partition_expression,
pg_get_expr(p.partexprs, c.oid, true) as sub_partition,
parent,
parent_schema,
case p.partstrat
when 'l' then 'LIST'
when 'r' then 'RANGE'
end as sub_partition_strategy
from inh
join pg_catalog.pg_class c on inh.inhrelid = c.oid
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left join pg_partitioned_table p on p.partrelid = c.oid
order by n.nspname, c.relname