Get all partition names for a table

Question

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1.
I was able to generate a count of partitions using this related answer by Frank Heikens.

I have a table foo with an insert trigger that creates foo_1, foo_2 etc. dynamically. The partition for insert is chosen based on the primary key id, a range based partitioning.

Is it possible to display all partitions currently in place for table foo?

András Váczi · Accepted Answer · 2014-04-25T15:03:01.117

52

Use the first query from the answer you linked and add a simple WHERE clause to get the partitions of a single table:

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

edited Apr 25 '14 at 15:03

answered Apr 22 '13 at 14:45

András Váczi

31,278
13
101
147

Is there something specific to partitions here or does this just give all tables that inherit from the parent? – Andrew Nov 28 '22 at 22:32
@Andrew in the past 10 years Postgres has seen a lot of progress in partitioning. I'm not sure without proper checking this answer is still useful nowadays... However, your question is a good one, and I think you are right assuming that 'plain' inheritance will be shown in the query output. – András Váczi Mar 23 '23 at 14:26
2

There is a relispartition flag on the pg_class table that you could reference but in practice if you try to inherit from a partitioned table then you get the message "cannot inherit from partitioned table". So if you know the table is partitioned then you would only get its partitions. If you wanted to verify that the parent is a partitioned table then you could add parent.relkind = 'p' to the where clause. – bygrace Dec 05 '23 at 19:17

Erwin Brandstetter · Answer 2 · 2020-06-27T23:41:05.700

Use the object identifier type regclass for a very simple query:

SELECT inhrelid::regclass AS child -- optionally cast to text
FROM   pg_catalog.pg_inherits
WHERE  inhparent = 'my_schema.foo'::regclass;

Lists all child tables of given parent table parent_schema.foo. Schema-qualification is optional, the search_path decides visibility if missing.

Similarly, returned table names are schema-qualified and escaped automatically where necessary. Safe, fast and simple.

The solution also works for declarative partitioning in Postgres 10 or later because, quoting the manual:

Individual partitions are linked to the partitioned table with inheritance behind-the-scenes;

Aside, to display the source table for any row retrieved from any table:

SELECT tableoid::regclass AS source, *
FROM   my_schema.foo
WHERE  <some_condition>;

Thanks! This works on Postgres 12.7.10. – Binita Bharati Sep 22 '21 at 07:19 — Binita Bharati, Sep 22 '21 at 07:19

Get all partition names for a table

2 Answers2

Linked