1

In Postgres, I want to do a bunch of deletes and writes in a transaction, but I want to fail the transaction if a row I am intending to delete does not exist. What is the best way to do this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
craigpastro
  • 795
  • 5
  • 14
  • Where are you running your query from? A tool? App code? CLI? Please show some of your SQL so we can better understand what you want to do. – Bohemian Apr 15 '22 at 00:56
  • Where you are using transactions? Because in PostgreSQL you can not use transactions inside the functions. In Functions keyword "begin" means a "start transaction" and the keyword "end" means a "commit transaction". If your function has any type of exceptions then the transaction is automatically rollbacked. You can use transactions only inside the procedures. – Ramin Faracov Apr 15 '22 at 01:45

2 Answers2

2

Use a PL/pgSQL code block (in a FUNCTION, PROCEDURE or DO statement) and raise an exception if your DELETE did not find any rows. You can use the special variable FOUND:

DO
$do$
BEGIN
   DELETE FROM tbl1 WHERE id = 1;
   
   IF NOT FOUND THEN
      RAISE EXCEPTION 'Failed to delete!';
   END IF;
   
   INSERT INTO tbl2 (col1) VALUES ('foo');
END
$do$;

Raising an exception rolls back the whole transaction.

The manual:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

This idea will cause an exception if no row is deleted:

delete from mytable
where id = 123
and 1/(select count(*) from mytable where id = 123) > 0

This works by the subquery, which should have the same where clause as the delete's where clause, returning 0 if there are no matching row(s) to delete, which causes a divide by zero error that will automatically fail the transaction causing a rollback of all work.

Bohemian
  • 412,405
  • 93
  • 575
  • 722