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?