postgresql 获取有关表分区的信息

uajslkp6 于 2023-05-17 发布在 PostgreSQL

关注(0)|答案(2)|浏览(154)

我有一个分区表超出了主表的使用范围。

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

如何使用查询postgres来获取Values范围信息。
我使用了一个查询，它至少为我提供了主表和分区表的信息，但我似乎无法找到访问分配给表partition 1的值范围**FROM（'2017 -01-01 00：00：00'）TO（'2050 -01-01 00：00：00'）**的方法
用于获取分区表信息的查询

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;

postgresql

来源：https://stackoverflow.com/questions/52669142/get-information-about-table-partitions

2条答案

按热度按时间

rwqw0loc1#

下面的查询也提供了有关分区的信息。从那里它只是字符串操作，以获得进一步的信息。注意：需要在查询中更改表名。

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

赞(0）回复(0）举报 2023-05-17

u5rb5r592#

具有parent_schema和仅表relkind的版本：

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

赞(0）回复(0）举报 2023-05-17

我来回答

postgresql 获取有关表分区的信息

2条答案

相关问题

热门标签

最新问答