8

I have a table res_users with primary key id

I would like to list all the foreign key link to id from all the tables in my database.

1 Answer 1

12

You can query the catalog tables:

SELECT la.attrelid::regclass AS referencing_table,
       la.attname AS referencing_column
FROM pg_constraint AS c
   JOIN pg_index AS i
      ON i.indexrelid = c.conindid
   JOIN pg_attribute AS la
      ON la.attrelid = c.conrelid
         AND la.attnum = c.conkey[1]
   JOIN pg_attribute AS ra
      ON ra.attrelid = c.confrelid
         AND ra.attnum = c.confkey[1]
WHERE c.confrelid = 'res_users'::regclass
  AND c.contype = 'f'
  AND ra.attname = 'id'
  AND cardinality(c.confkey) = 1;
1
  • 1
    cool... really thanks. Commented Apr 27, 2020 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.