0

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

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

SELECT
  confrelid :: REGCLASS,
  af.attname AS fcol,
  conrelid :: REGCLASS,
  a.attname  AS col
FROM pg_attribute af, pg_attribute a,
  (SELECT
     conrelid,
     confrelid,
     conkey [i]  AS conkey,
     confkey [i] AS confkey
   FROM (SELECT
           conrelid,
           confrelid,
           conkey,
           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.

SELECT
  conrelid :: REGCLASS,
  conname,
  pg_get_constraintdef(oid)
FROM pg_constraint
WHERE confrelid = 'my_table' :: REGCLASS;

producing

  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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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