CODEWITHSUNDEEP
postgresqlreverse-engineeringpartitioning
danjuggler
danjuggler

Reputation: 1320

Find all partition tables "inheriting" from master table

Say I have a table, "foo", with partition tables "foo1", "foo2", and "foo3". But at the current moment all I know is there are parition tables which inherit from table "foo". How can I find that foo has 3 partitions, foo1, foo2, and foo3?

Upvotes: 4

Views: 7226

Answers (4)

user330315
user330315

Reputation:

Starting with Postgres 12, there is a built-in function:

select *
from  pg_partition_tree('the_table'::regclass)
where parentrelid is not null;

Upvotes: 3

Le Droid
Le Droid

Reputation: 4774

To list all your partitions (child tables) - tested with PG v9-v13:

SELECT c.relname FROM pg_inherits i JOIN pg_class p ON i.inhparent = p.oid
JOIN pg_class c ON i.inhrelid = c.oid WHERE p.relname='parentTableName';

Upvotes: 3

klin
klin

Reputation: 121514

Use pg_inherits. Example:

create table my_parent_table (id int);
create table my_child_table_no_1 (check (id < 10)) inherits (my_parent_table);
create table my_child_table_no_2 (check (id >= 10)) inherits (my_parent_table);

select relname
from pg_inherits i
join pg_class c on c.oid = inhrelid
where inhparent = 'my_parent_table'::regclass

       relname       
---------------------
 my_child_table_no_1
 my_child_table_no_2
(2 rows)

You can also select check constraints using pg_constraint:

select relname "child table", consrc "check"
from pg_inherits i
join pg_class c on c.oid = inhrelid
join pg_constraint on c.oid = conrelid
where contype = 'c'
and inhparent = 'my_parent_table'::regclass

     child table     |   check    
---------------------+------------
 my_child_table_no_1 | (id < 10)
 my_child_table_no_2 | (id >= 10)
(2 rows)

Upvotes: 2

A. Scherbaum
A. Scherbaum

Reputation: 855

The way I dig into such catalog information is by using psql. Start psql with the -eE options:

psql -eE <your database>

And then show your table:

\d <your table>

This will list all the queries which psql generates to fetch the information from the catalog. This includes inherited tables.

Keep in mind that it is possible that the catalog changes from one major version to another - although for such basic functionality it is unlikely.

Upvotes: 0

Related Questions