32

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?

0

2 Answers 2

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';
3
  • Is there something specific to partitions here or does this just give all tables that inherit from the parent?
    – Andrew
    Nov 28, 2022 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. Mar 23, 2023 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 5, 2023 at 19:17
30

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>;
1
  • Thanks! This works on Postgres 12.7.10. Sep 22, 2021 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.