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.


From http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no

  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 '15 at 12:46
  • Thanks, fixed the query, but I am not sure if I can explain it. – Andrei Dec 3 '15 at 13:35
  • I just hope there is something more simple. This one is from 2008. – Andrei Dec 3 '15 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 '15 at 14:34
  • No, not a problem, but if there is something nicer in 2015, I would like to know it :) – Andrei Dec 3 '15 at 14:38

Your Answer

