Question :
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).
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'))