Postgres variable for multiple DELETE statements

Question

We have some tables that don't have cascade set up, but we'd like to manually clean them up with one command when a project is deleted.

For example, to delete the Permissions table and the Votes table based on the project with identifier = 'abcdefg', we'd like to first look up the project's id and then use that to delete the tables in question:

WITH proj_id AS (
  SELECT id FROM "Projects" WHERE identifier = 'abcdefg'
)

DELETE FROM "Permissions" WHERE project_id IN (proj_id);
DELETE FROM "Votes" WHERE project_id IN (proj_id);
DELETE FROM "Gadgets" WHERE project_id IN (proj_id);
...
DELETE FROM "Projects" WHERE id IN (proj_id);

This doesn't work, but I think it illustrates the goal.

Is there a way to accomplish this DELETE across multiple tables, without writing the identifier value more than once?

Thom Brown · Accepted Answer · 2020-05-12 16:36:03Z

10

Move the "Projects" deletion into the WITH clause, and get that to return the proj_id column. Then separate your DELETE clauses with a comma to make them all part of your common table expression.

For example:

WITH proj_id AS (
  DELETE FROM "Projects"
  WHERE identifier = 'abcdefg'
  RETURNING id
),
perm_del AS (
  DELETE FROM "Permissions" p
  USING proj_id
  WHERE p.project_id = proj_id.id),
vote_del AS (
  DELETE FROM "Votes" v
  USING proj_id
  WHERE v.project_id = proj_id.id),
gadg_del AS (
  DELETE FROM "Gadgets" g
  USING proj_id
  WHERE g.project_id = proj_id.id),
...
SELECT id AS deleted
FROM proj_id;

edited May 12, 2020 at 16:36

answered May 12, 2020 at 16:13

Thom Brown

2,0698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tyler Over a year ago

I get syntax error at or near "RETURNING", and if I delete that line (and the ellipsis), I get syntax error at or near "SELECT" for SELECT id AS deleted

Thom Brown Over a year ago

Looks like I neglected to convert that statement into a DELETE. I've updated it now.

Tyler Over a year ago

Thanks for the update; still getting the 2nd error ERROR: syntax error at or near "SELECT" LINE 18: SELECT id AS deleted

Thom Brown Over a year ago

The final CTE shouldn't have a comma after it. Try removing the comma on the end of the line before that SELECT.

informaticienzero Over a year ago

Indeed, the last comma was the reason of the error. Thanks for pointing it.

Collectives™ on Stack Overflow

Postgres variable for multiple DELETE statements

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related