12

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?

19

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';
| improve this answer | |
8

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>;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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