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?

1 Answer 1


First start a transaction:


Start deleting all from links_groupseen first:

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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