5

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance:

CREATE TABLE person (id int PRIMARY KEY);
CREATE TABLE sale (id int PRIMARY KEY,
    person_id int REFERENCES person(id) ON UPDATE CASCADE);

Now, what I am trying to do is: Remove duplicate entries from the person table, fixing the references sale references. For instance, using this data:

INSERT INTO person values (1), (2), (3);
INSERT INTO sale VALUES (11, 1), (12, 2), (13, 3);

Person 1 and 2 are actually the same, so they should be 'merged'. By merging I mean excluding person 2 while updating the references for person 1 in the sale table.

Since there is an ON UPDATE CASCADE on the table that references person, I wonder if there is some way to, without having to manually update the sale table, cause the cascade update while deleting the person 2

What I am trying to achieve here is avoiding a dedicated procedure to update the references since the number of referring tables will most likely increase, requiring maintenance in this procedure (and it is quite big already).

  • By what criteria is a person with different ids the same? – Mihai Sep 8 '14 at 20:47
  • The user will define. They may have the exact same name, or similar names (with typos), missing a middle name, etc... – romaia Sep 9 '14 at 15:34
4

No, there is no built-in feature for merging one key into another, updating all referencing rows automatically. There could be all kinds of complications you need to resolve.

Simple case

You need to run UPDATE on all referencing tables and DELETE on the referenced table. The simple case would look like this:

BEGIN;

--  1. update references
UPDATE sale
SET    person_id = _org_id
WHERE  person_id = _dupe_id;

-- 2. kill dupe 
DELETE FROM person
WHERE  id = _dupe_id;

COMMIT;

But there may be all kinds of ...

Complications

Imagine a table person_tag implementing an n:m relationship between person and tag with a unique constraint on (person_id, tag_id). You can't UPDATE rows that would result in duplicate tags. You need to resolve conflicts: only update rows that do not conflict with the unique constraint and delete the rest.

-- example for conflict resolution
UPDATE person_tag pt
SET    person_id = _org_id
WHERE  person_id = _dupe_id
AND NOT EXISTS (
   SELECT 1 FROM person_tag
   WHERE  person_id = _org_id
   AND    tag_id = pt.tag_id
   );

DELETE FROM person_tag
WHERE  person_id = _dupe_id;
-- end person_tag

There may be more sophisticated constellations ...

Full automation

If you have lots of referencing tables that can be treated in the same fashion (or a changing number), you can automate the procedure with dynamic SQL.

For the simple case with a single-column fk constraint and no complications:

CREATE OR REPLACE FUNCTION f_merge_id(_tbl regclass, _col text, _org_id int, _dupe_id int)
  RETURNS void AS
$func$
DECLARE
   rec record;
BEGIN
   FOR rec IN  -- find all referencing columns
      SELECT c.conrelid::regclass AS tbl, quote_ident(a2.attname) AS col
      FROM   pg_catalog.pg_attribute  a1
      JOIN   pg_catalog.pg_constraint c  ON c.confrelid = a1.attrelid
                                        AND c.confkey   = ARRAY[a1.attnum]
      JOIN   pg_catalog.pg_attribute  a2 ON a2.attrelid = c.conrelid
                                        AND a2.attnum   = c.conkey[1]
      WHERE  a1.attrelid = _tbl
      AND    a1.attname  = _col
      AND    c.contype   = 'f'  -- fk constraint
   LOOP        -- Redirect to _org_id all references to dupe_id
      EXECUTE format('
         UPDATE %1$s
         SET    %2$s = $1
         WHERE  %2$s = $2'
        ,rec.tbl, rec.col)
      USING _org_id, _dupe_id;
   END LOOP;

   -- Finally kill (now orphaned) dupe 
   EXECUTE format('DELETE FROM %s WHERE %s = $1', _tbl, _col)
   USING _dupe_id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_merge_id('person', 'person_id', _org_id := 1, _dupe_id := 2);

This updates any number of tables that have a foreign key referencing the master table.

Using named parameters in the call, which is not required, but you'll want to make sure not to confuse original and duplicate here.

SQL Fiddle.

Major points

  • I was expecting that the final solution should be instrospecting the schema using the catalog, but I hoped there would be a way to take advantage of the UPDATE CASCADE. I also didn't consider the mapping table problem. Nicely put. – romaia Sep 9 '14 at 15:44
  • @romaia: That's not possible, because the referenced filed(s) must be unique, so you cannot update one row with an existing value of another. There might be some trickery with multi-column fk constraints, but nothing as clean and cheap as my function ... – Erwin Brandstetter Sep 9 '14 at 15:49

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.