1

I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated

For instance, if I run DELETE FROM links_reply WHERE id=3;, I get a violates foreign key constraint error.

But when I run DELETE FROM links_groupseen WHERE which_reply_id=3; and then run DELETE FROM links_reply WHERE id=3;, everything's fine. Delete the child first, then the parent. Works!

Things get complicated though, when the respective tables are huge, and I'm trying to delete all rows before a certain date. E.g. I want to DELETE FROM links_reply WHERE "submitted_on" < now() - interval '30 days';. In this particular case, how do I take care of 'the child table' first? What command do I run?

2

First start a transaction:

begin;

Start deleting all from links_groupseen first:

delete 
from links_groupseen 
where which_reply_id in (select myproperid from links_reply where "submitted_on" < now() - interval '30 days')

and then delete from links_reply:

DELETE 
FROM links_reply 
WHERE "submitted_on" < now()- interval '30 days';

and if you're happy with what you see, you can COMMIT. The reason I'm using a transaction is that within a transaction, now() will give you the same values at each call. Otherwise, you would have to replace now with a constant MYDATE value of your choice on both delete statements.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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