PostgreSQL – How to Find Child Tables for a Specific Table

foreign keypostgresql

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

SELECT * 
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).

Best 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')

to 

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