Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.

I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will be stored automatically. Since restrictions has a serial id, there will be no duplicates.

How do I write such a trigger in PostgreSQL?

share|improve this question
up vote 14 down vote accepted

You just need to move the old data into the restrictions_deleted table before it gets deleted. This is done with the OLD data type. You can use a regulat INSERT statement and and use the OLD values as the values-to-be-inserted.

BEFORE DELETE ON restrictions 

CREATE FUNCTION moveDeleted() RETURNS trigger AS $$
       INSERT INTO restrictions_deleted VALUES(OLD.column1, OLD.column2,...);
       RETURN OLD;
$$ LANGUAGE plpgsql;
share|improve this answer
You can replace the explicit list of column values with something like VALUES((OLD).*) – KayEss Apr 16 '15 at 4:26
Works fine, but the create function needs to be called before create trigger. And the VALUES((OLD).*) trick suggested by KayEss is nice. – mivk Apr 2 at 14:14

If you are open to a different approach, Have you considered adding a 'deleted' Boolean flag to the table, or a 'deleted_at' timestamp instead.

Or better still, deny CRUD access to your database tables and handle the audit trail in your transactional API :)

share|improve this answer
+1 Thanks - wouldn't work for my case, but it's a nice approach that uses a single table only. – Adam Matan May 5 '11 at 10:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.