In theory you can, but in practice you'll find that undoing individual changes is kind of hard to get right because of foreign keys, etc.
CREATE TABLE a (
id integer primary key
CREATE TABLE b (
id integer primary key,
a_id integer not null references a(id) on delete cascade
INSERT INTO a(id) VALUES (1),(2),(3);
INSERT INTO b(id, a_id) VALUES (10,1), (20,2), (30,3);
Now, say you do a:
DELETE FROM a WHERE id = 2;
This will cause an automatic delete of
b. When you want to undo this change, you need to know that the delete from
a also caused a delete in
Similarly, say you:
DELETE FROM b WHERE id = 30;
then separately and later, someone else does a:
DELETE FROM a WHERE id = 3;
If you want to undo the first delete it'll fail unless you undo the second delete first.
Basically, you can only reliably undo an operation if you undo all subsequent operations in the order that they occurred first. Even then, it's hard to get right.
That's before you even get to thinking about schema changes. If you add a new
NOT NULL column to a table, then you want to undo a
DELETE that happened before the column was added, what do you do? Give the new row the default value in the column? What if there's no
I'd record an audit history but wouldn't generally attempt to support undo.
You might want to investigate PostgreSQL's point-in-time recovery features for a way to do database-wide rollbacks or restores to points in the past.