Re: [NOVICE] How to list partitions of a table in PostgreSQL 10

Stephen Froehlich schrieb am 31.10.2017 um 20:59:
> I have discovered a simple query that will tell me if a table is a
> registered partition or not, which meets my purposes for now, but a
> list of partitions of a given table would be better:
> 
> SELECT 1 FROM pg_class WHERE relname = '[attached_partition_name_here]' AND relpartbound IS NOT NULL;

I don't know if this is the most efficient query, but it works for me:

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'          ---<<
changetable schema here    and cl.relname = 'the_table_name'   ---<< 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)
aspartition_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
leftjoin pg_partitioned_table p on p.partrelid = c.oid
 
order by n.nspname, c.relname




-- 
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice