Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to list all the Partitions created by dynamic triggers in Postgresql 9.1
I was able to generate a count of partitions for tables which are partitioned using Frank Heikens' answer in http://stackoverflow.com/questions/8634144/get-number-of-partitions-in-postgresql-database

Let me explain, i have a table "foo",i have a insert trigger defined on foo which creates "foo_1" "foo_2",... dynamically.
The partition for insert is chosen based on the primary key id, a range based partitioning.
So is it possible to display all partitions currently in place for the table "foo"?

share|improve this question
Probably I don't understand your question completely, but the first query there gives you the result. Or you want to distinguish between user-created and trigger-created tables? –  dezso Apr 19 '13 at 11:55
The first query which you mention shows all the Partitions in a DB. I would like to display for a particular table. –  Vishnu Kumar Apr 22 '13 at 10:28
Then simply add WHERE parent.relname = 'the_table_you_are_looking_for' –  dezso Apr 22 '13 at 11:56
Ya that works, didnt know which relname to use. Thanks dezso. Do i add the correct SQL as answer?? –  Vishnu Kumar Apr 22 '13 at 13:48
add comment

1 Answer

up vote 1 down vote accepted

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

    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child,
    child.relname       AS child_schema
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';
share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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