postgres finding child tables for particular table

Posted on

Question :

I want to find all child tables corresponding to particular parent table.

FROM information_schema.table_constraints tc 
  right JOIN information_schema.constraint_column_usage ccu 
          ON tc.constraint_catalog=ccu.constraint_catalog 
         AND tc.constraint_schema = ccu.constraint_schema 
         AND tc.constraint_name = ccu.constraint_name 
         and ccu.table_name in ('plugin') 
 WHERE lower(tc.constraint_type) in ('foreign key');

Now this is working fine in public schema. but when I use 'a.plugin' instead of 'plugin' where a is my schema then it is not working. (i.e it is not working inside particular schema when I try to use table inside schema as a parent table).

Answer :

The schema name of a table is not stored in the column table_name, it’s stored in the column table_schema, so you need to change the condition:

and ccu.table_name in ('plugin') 


and (ccu.table_schema, ccu.table_name) in (('a', 'plugin'))

Leave a Reply

Your email address will not be published. Required fields are marked *