Deleting aged postgresql rows with foreignkey constraints

Question

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?

Answer 1

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.