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

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to list partitions of a table in PostgreSQL 10
Date: 2017-10-31 20:10:24
Message-ID: otalb9$5ma$1@blaine.gmane.org
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-novice

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' ---<< change table 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) 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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Stephen Froehlich 2017-11-04 18:35:20 Old solutions for listing tables by tablespace broken in PG 10 for partitions.
Previous Message Stephen Froehlich 2017-10-31 19:59:59 Re: How to list partitions of a table in PostgreSQL 10