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
