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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 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_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';
share|improve this answer

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

List all child tables of

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

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

Similarly, the string Postgres outputs is schema-qualified and escaped automatically where necessary.
Very safe, fast and convenient.

BTW, to display the source table for any row retrieved form the parent table via inheritance, use:

SELECT tableoid::regclass AS source, *
WHERE <some_condition>;

(Or any row from any table at all, really.)

share|improve this answer

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.