Re:Easy, alternative soft deletion: `deleted_record_insert`
by aidos on Sunday January 01, 2023 @07:28AM
(#34205784)
I’m also not sure about the foreign key restrictions on generated columns (and glancing at the docs I don’t see anything about it on there) but for all intents and purposes they are real columns so I’d imagine it probably works. Apparently they run after the before triggers, I’m not totally sure where foreign keys are checked but probably after that?
As an aside, they’re a great feature. We’re using them to generate columns that we can index for efficient joins between tables and also for creating text strings for searching over using trigram indexes. The whole thing is really seamless.
Re:Easy, alternative soft deletion: `deleted_record_insert`
by simcop2387 on Sunday January 01, 2023 @07:41AM
(#34205848)
I haven't tested it but it might be the case that it'd need to be a stored generated column to be referenced like that but that shouldn't be a big deal.
There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status <> 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, populate both id and id_active to same value; when you soft-delete it, set id_active to NULL. Actually, maybe a simpler solution is to make id_active a "GENERATED ALWAYS AS ... STORED" column–although I'm not sure if Postgres supports them for foreign keys? That's a relatively new Postgres feature and I haven't done much yet with the more recent versions in which that feature was added.