You read my mind. Yes, this is possible. I haven’t done it in prod, but I made a PoC. As to table inheritance, yes, you can also do that, and yes, in PG anyway it automatically propagates schema changes.
There are quite a few gotchas [0], though, so think carefully before doing so.
The below assumes you have a user/customer table named customer, and another copy named inactive_customer. One other note: if you use an IDENTITY for the PK in the main table, you’ll want to drop that first in the inactive_customer table, so the ids match. Example below.
ALTER TABLE inactive_customer ALTER COLUMN id DROP IDENTITY;
CREATE FUNCTION move_inactive_customer()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
BEGIN
INSERT INTO inactive_customer(<schema>) —-replace schema with yours
VALUES (OLD.<col> for col in cols) —-replace this pseudo-loop with your schema
ON CONFLICT (id) DO NOTHING —-modify for your table constraints
RETURN OLD;
END;
$function$;
CREATE TRIGGER redirect_customer
BEFORE DELETE
ON customer
FOR EACH ROW
EXECUTE PROCEDURE move_inactive_customer();
You read my mind. Yes, this is possible. I haven’t done it in prod, but I made a PoC. As to table inheritance, yes, you can also do that, and yes, in PG anyway it automatically propagates schema changes.
There are quite a few gotchas [0], though, so think carefully before doing so.
The below assumes you have a user/customer table named customer, and another copy named inactive_customer. One other note: if you use an IDENTITY for the PK in the main table, you’ll want to drop that first in the inactive_customer table, so the ids match. Example below.
[0]: https://www.postgresql.org/docs/current/ddl-inherit.html