How to combine two records while keeping all referencing records?

PostgreSQL 11.1

I have struggled with this problem for a long time. (I have tried to improve the question from before).

Problem: A person has two different names in table tempA. Each name has its associated records in table tempB. How can I move all the records associated with one name to the other name, then delete the name?

Example: I have two names –“Tom” and “Bob”. I want to change all the records associated with “Bob” to “Tom”, then remove “Bob” from the database.

How is this done while keeping the associated records in table tempb?

CREATE TABLE tempA
(
    id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);


CREATE TABLE tempb
(
    id serial PRIMARY KEY,
    tempa_id integer NOT NULL,
    description text NOT NULL,
    CONSTRAINT foo_bar_fk FOREIGN KEY (tempa_id)
        REFERENCES tempa (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

INSERT INTO tempA (name) VALUES('tom');
INSERT INTO tempA (name) VALUES('bob');

INSERT INTO tempB (tempA_id, description) SELECT id, 'test1' FROM tempA WHERE tempA.name = 'tom';
INSERT INTO tempB (tempA_id, description) SELECT id, 'test2' FROM tempA WHERE tempA.name = 'tom';
INSERT INTO tempB (tempA_id, description) SELECT id, 'test3' FROM tempA WHERE tempA.name = 'bob';
INSERT INTO tempB (tempA_id, description) SELECT id, 'test4' FROM tempA WHERE tempA.name = 'bob';

Initial set:
-- tempA
id  name
1   "tom"
2   "bob"

id tempA_id  description
1   1      "test1"
2   1      "test2"
3   2      "test3"
4   2      "test4"

The goal I am trying to reach is:

--Desired Results
-- tempA
id  name
1   "tom"

-- tempB
id tempA_id  description
1   1       "test1"
2   1       "test2"
3   1       "test3"
4   1       "test4"

This is what I’ve tried, but it continues to fail:

BEGIN;

SET CONSTRAINTS ALL  DEFERRED; 

-- from 'tom' to 'bob' -- when all is done 'tom' must be the name to keep.
WITH _in (name1, name2) AS(
        VALUES('tom','bob')
),
 _bob  AS(                 -- DELETING 'bob' record FROM tempA. 
        DELETE FROM tempA                     
        USING _in 
        WHERE (tempA.name = _in.name2)
        RETURNING tempA.*
)
UPDATE tempA        -- REPLACING 'bob' with 'tom'. REPLACING 'bobs' id with 'toms' id.
SET name = _in.name1, id = _tom.id
FROM _in 
JOIN _bob ON (_bob.name = _in.name2)
JOIN tempA _tom ON (_tom.name = _in.name1)
WHERE (tempA.id = _bob.id);

COMMIT;

ERROR: update or delete on table “tempa” violates foreign key constraint “foo_bar_fk” on table “tempb” DETAIL: Key (id)=(2) is still referenced from table “tempb”.

It seems I cannot get the UPDATE to occur before the Delete is enforced.

Any help is most appreciated. TIA

Answer

You would need to update the child table first, then delete from the parent – the foreign key constraints prevents you from proceeding the other way around.

Consider:

with 
    names (to_keep, to_del) as(values('tom','bob')),
    upd as (
        update tempB
        set tempA_id = a_keep.id
        from names n
        inner join tempA a_keep on a_keep.name = n.to_keep
        inner join tempA a_del  on a_del.name  = n.to_del
        where tempB.tempA_id = a_del.id
        returning a_del.id
    )
delete from tempA 
using upd
where tempA.id = upd.id

Demo on DB Fiddle