6

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?

14

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

Using the object identifier type regclass, this can be simplified considerably:

List all child tables of parent_schema.foo:

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

The table name you provide for the cast to regclass can optionally be schema-qualified. If not, the search_path decides visibility.

Similarly, returned table names are schema-qualified and escaped automatically where necessary.
Very safe, fast and convenient.

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

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

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.