I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row.

How do I get a list of foreign keys related to my table?

I have seen a number of old posts with heavy queries and wonder if there is an easier way nowadays?

Update: I have added an answer with a working query and would be happy if there is something more simple.

1 Answer 1


From http://www.postgresql.org/message-id/[email protected]

  confrelid :: REGCLASS,
  af.attname AS fcol,
  conrelid :: REGCLASS,
  a.attname  AS col
FROM pg_attribute af, pg_attribute a,
     conkey [i]  AS conkey,
     confkey [i] AS confkey
           generate_series(1, array_upper(conkey, 1)) AS i
         FROM pg_constraint
         WHERE contype = 'f') ss) ss2
WHERE af.attnum = confkey 
  AND af.attrelid = confrelid 
  AND a.attnum = conkey 
  AND a.attrelid = conrelid
  AND confrelid :: REGCLASS = 'my_table' :: REGCLASS 
  AND af.attname = 'my_referenced_column';

It produces

   confrelid   |         fcol         |   conrelid    |     col
   my_table    | my_referenced_column | another_table | some_column

Update: RhodiumToad on freenode #postgres channel gave a simpler query which however needs some parsing and filtering if only specific column is needed.

  conrelid :: REGCLASS,
FROM pg_constraint
WHERE confrelid = 'my_table' :: REGCLASS;


  conrelid  |       conname        |         pg_get_constraintdef
 some_table | some_constraint_name | FOREIGN KEY (some_column) REFERENCES my_table(my_referenced_column) DEFERRABLE INITIALLY DEFERRED
  • The beginning of the query and an explanation are missing.
    – dezso
    Dec 3, 2015 at 12:46
  • Thanks, fixed the query, but I am not sure if I can explain it.
    – Andy
    Dec 3, 2015 at 13:35
  • I just hope there is something more simple. This one is from 2008.
    – Andy
    Dec 3, 2015 at 13:41
  • Well, the second one is simpler, true - however, the first one is a better fit for scripting or further (non-human) processing. It cannot get much simpler in this case, I'm afraid. (Also, I don't see why complexity is a problem here.)
    – dezso
    Dec 3, 2015 at 14:34
  • No, not a problem, but if there is something nicer in 2015, I would like to know it :)
    – Andy
    Dec 3, 2015 at 14:38

Your Answer

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

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