I have a few tables in postgres that refer to each other. I want to set up a mechanism to "archive" rows in one of my tables. That is, I want to still hold onto the data and be able to read from it, but I don't want to be able to edit that row anymore or edit the foreign keys in other tables to reference this now "archived" row.

Is this something that can be achieved? Essentially, I want the rest of the database to act like this row's primary key is no longer there, the same way that if you try to set an invalid foreign key, postgres will throw an error that that key was not found in the referenced table.

Thanks

EDIT: I don't want to actually archive any of the data. I say "archive" because I can't think of a better way to describe it. Essentially, I just want to be able to change a bool value in a row of the table and then that signals to postgres to no longer allow any changes to that row or use that rows id as a foreign key in any other tables. The only thing that someone should be able to do would be to change that bool back to true and then interact normally.

CodePudding user response：

Add a flag column, either a bool or an enum jf there are multiple states. Postgres won't check this for you, you have to add a where clause to every applicable query.

This is error prone. You can make it safer by defining a view which already has the where clause. Do all queries on that view. Rename the table to something like "table_all" and let the view use the table's name, then all existing queries will Just Work.