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 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
    
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
1  
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
    
So, you are using inheritance, right? –  Erwin Brandstetter Apr 25 at 16:25

2 Answers 2

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:

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';
share|improve this answer
    
A little correction, an error about child namespace and child name. It should be: nmsp_child.nspname AS child_schema, child.relname AS child –  Mavlarn Apr 25 at 10:18
    
@Mavlarn thanks for pointing it out, after one year of staying here with that error :) Now fixed. –  dezso Apr 25 at 15:03

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, 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, *
FROM   my_schema.foo
WHERE <some_condition>;

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

share|improve this answer

Your Answer

 
discard

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.