Let's assume a scenario with the entities
Company and a table
PersonCompanyStocks that models how many stocks a persons owns of a certain company (N:M cardinality). For example:
person | company | num_stocks ----------------------------- Alice | foo | 300 Bob | foo | 100 Bob | bar | 200
This table uses
(person, company) as a primary key to guarantee unique entries, and foreign keys to the respective person/company table (I'm only using string IDs for simplicity).
Now let's assume company
bar buys company
foo. We want to update the table in a way that it becomes:
person | company | num_stocks ----------------------------- Alice | bar | 300 Bob | bar | 300
Looking only at
Alice's record suggests to use a naive approach like:
UPDATE PersonCompanyStocks SET company = "bar" WHERE company = "foo"
However this update fails with
duplicate key value violates unique constraint ... because for
Bob there already is a row with the key
("Bob", "bar"). For
INSERT's Postgres supports
ON CONFLICT DO ..., but it looks like there is no equivalent for
UPDATE. And clearly we also have to deal with properly merging the
num_stock value of the two rows.
What is the best strategy to approach this problem? I only see a relatively ugly solution:
- One query to determine the duplicates.
UPDATEto merge the duplicates into the final row.
DELETEto remove the offending duplicates.
- The above
UPDATEto do the renaming in rows without duplicates.
This feels complex and is probably prone to race conditions. Does Postgres offer any trick to solve this more elegantly?