1

I want to delete a single row from a table in PostgreSQL 9.5.0.0, but there are a number of other items in the database that reference it as a foreign key. I'd like to delete them all, i.e. cause the deletion to cascade.

The usual way to do that would be to add ON DELETE CASDADE to the table definition, but unfortunately I'm not in a position to make changes to the database schema (don't want to create migration headaches for my coworkers). Is there any way to do this in a single SQL query?

This question has been asked before on SO (Cascading deletes in PostgreSQL) but since that was 7 years ago I thought I would bring it up again. Any chance the newer versions of Postgres allow you to do this?

Community
  • 1
  • 1
ninebelowzero
  • 11
  • 4
  • 1
    No. `ON DELETE CASCADE` is the provided way to do this. The alternative is to, within a transaction, delete the rows from the dependent tables first. If there are several tiers of dependent tables, then this may be quite an involved operation. – Ben Aug 15 '16 at 12:15
  • Looks like I will have to mess with the schema then. Thanks for your answer. – ninebelowzero Aug 16 '16 at 12:31
  • Or you could write a stored procedure to do it. – Ben Aug 16 '16 at 15:18
  • 1
    You could look at variations of this - WITH x AS ( DELETE FROM point WHERE evt_id = 1139 RETURNING pt_id ) DELETE FROM trace USING x WHERE trace.pt_id = x.pt_id;. Taken from http://stackoverflow.com/questions/10145221/how-to-delete-data-from-multiple-tables – Jayadevan Aug 17 '16 at 03:47
  • Thanks. I appreciate these tips - is it possible to upvote them, as they're only comments and not answers? – ninebelowzero Aug 22 '16 at 08:26

0 Answers0