Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs

My use-case is that I have a "shadow" table of an existing, concrete table. I'd like to update the shadow table on every UPDATE statement on the concrete table. I know I can use a per-row trigger to accomplish this. Can I use a per-statement trigger instead?

As a concrete example on Postgres 13.3, I'd like something like:

CREATE TABLE product (id bigint, val text /* many more columns */);
CREATE TABLE shadow_product (id bigint, val text);

CREATE FUNCTION update_shadow_product() RETURNS trigger AS
  old_data text;
  new_data text;
  UPDATE shadow_product
  SET id = t.old_id, val = t.new_val
  FROM (
    -- How to handle case where ID changes between old and new?
    SELECT ot.id as old_id, nt.val as new_val  FROM oldtab ot
      INNER JOIN newtab nt USING (id)
  ) t
  WHERE id = t.old_id;

  RETURN NULL; -- per-statement triggers should always return null
$fn$ LANGUAGE plpgsql;

CREATE TRIGGER update_shadow_products_trigger
EXECUTE FUNCTION update_shadow_product();

As a test case:

-- Seed initial data.
INSERT INTO product (id, val) VALUES (1, 2), (10, 20);
INSERT INTO shadow_product SELECT id, val FROM product;

-- Update the primary key which makes it hard to match old to new rows.
UPDATE product SET id = 2 * id WHERE TRUE;

-- Show rows that differ between product and shadow_product.
-- Ideally there's no null values, indicating the tables are the same.
SELECT p.id, p.val, sp.id AS shadow_id, sp.val AS shadow_val
FROM product p
  FULL OUTER JOIN shadow_product sp USING (id, val)

 *  Returns:
 *  +----+----+---------+----------+
 *  |id  |val |shadow_id|shadow_val|
 *  +----+----+---------+----------+
 *  |NULL|NULL|1        |2         |
 *  |2   |2   |NULL     |NULL      |
 *  |NULL|NULL|10       |20        |
 *  |20  |20  |NULL     |NULL      |
 *  +----+----+---------+----------+

I'm not sure sure how to associate rows between oldtab and newtab if primary key changes. One thought I had was relying on row_number on oldtab and newtab without an order clause. Some quick tests seem to indicate the row_number order is stable but I'd prefer a more bulletproof method. Is there a better way?

1 Answer 1


The way to identity a table row is the primary key, and you can use that here if both tables have the same primary key. If the primary keys are automatically generated and different, use another unique constraint. After all, there should be a way to relate rows between those two tables.

  • The primary keys are the same so I can relate product to shadow_product using the id column. From there I can figure out the set of shadow_product rows to update but I don't know what to update them to because there's no way to map between oldtab and newtab (if the PK is updated). I suppose the mapping doesn't matter; I can just blindly update each old row in shadow_product with values from the newtab. That should work as long as count(oldtab) always equals count(newtab).
    – Joe
    Jul 22, 2021 at 5:29
  • Do you really need to change primary keys? If not, forbid it, then you can associate rows using the primary key. Jul 22, 2021 at 5:52
  • It's unlikely but yes, the primary keys might change. Another option: if the primary keys do change, give up with an update and delete all rows from oldtab and then insert all rows from newtab.
    – Joe
    Jul 23, 2021 at 7:25
  • That's a good idea. Perhaps transactions that modify a primary key (I would call this "surgery") don't modify many rows at once, then it wouldn't matter. Jul 23, 2021 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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