Hide rows in postgres

Question

I have decided that instead of deleting rows from a postgres database, i rather hide the rows so that in case the rows were deleted by accident, i can still retrieve them. What is the best approach to hide rows from a database? Do you recommend creating a column named "Active" and giving it a value of either 1 or 0, 1 meaning active and 0 meaning deleted and hidden. I'm also using sqlalchemy if that matters.

Answer 1

That's pretty much the only way to do it without actually deleting the rows.

You can create a view that shows you only the active entries, so you don't have to remember to add that to the where clause of every query.

Answer 2

You could hide the real table , create a view and use rules to do inserts, updates and deletes.

Suppose you have a session table. I would create a table called sessions_table, which contains all data and the "active"-column. Then you hav a view called session (CREATE view sessions AS SELECT [all fields withtout active] FROM sessions_table WHERE active = TRUE), as mentioned by Greg.

Now you can create a Rule: If you want to delete a row in sessions, you want to update a row in sessions_table instead:

CREATE RULE delete_session ON DELETE TO sessions DO INSTEAD UPDATE sessions_table SET active = FALSE WHERE id = OLD.id;

For inserting, you'll need:

CREATE RULE insert_session ON INSERT TO sessions DO INSTEAD INSERT INTO sessions_table (..., active) VALUES(..., TRUE);

And finally we have

CREATE RULE update_session ON UPDATE TO session DO INSTEAD UPDATE sessions_table SET ... WHERE id = new.id AND active = TRUE;

Now you call work fully on sessions and don't have to bother with the active-column.

Answer 3

Much better to have a "ValidBefore" timestamp than a flag.

Presumably the reason you want to save them is you dont trust your delete logic or users, and, you want to be able to recover the rows deleted when an error is discovered.

This is much easier to do if you have a timestamp or similar rather than just a flag.

Answer 4

How about having a hidden_rows_table that has the exact same structure as the active table? You could then move the rows from the active table to the hidden_rows_table when you delete them. To select all rows (both hidden and active) you could use UNION.

This solution is especially good if you have huge amounts of data, like in a data warehouse and you are worried about performance of for example creating new indexes.