Thread: [NOVICE] How to list partitions of a table in PostgreSQL 10
What is the syntax to get a list of the partitions of a table in PostgreSQL 10?
Thanks,
Stephen
Stephen Froehlich
Sr. Strategist, CableLabs®
s.froehlich@cablelabs.com
Tel: +1 (303) 661-3708
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;
--Stephen
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Stephen Froehlich
Sent: Tuesday, October 31, 2017 1:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] How to list partitions of a table in PostgreSQL 10
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing |
CableLabs WARNING: The sender of this email could not be validated and may not match the person in the "From" field.
What is the syntax to get a list of the partitions of a table in PostgreSQL 10?
Thanks,
Stephen
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
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;
--Stephen
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Stephen Froehlich
Sent: Tuesday, October 31, 2017 1:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] How to list partitions of a table in PostgreSQL 10
This sender failed our fraud detection checks and may not be who they appear to be. Learn about spoofing
CableLabs WARNING: The sender of this email could not be validated and may not match the person in the "From" field.
What is the syntax to get a list of the partitions of a table in PostgreSQL 10?
Thanks,
Stephen