In a Postgres transaction, what is the best way to delete if exists, and fail otherwise?-CodePudding

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?

CodePudding user response：

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:

Dynamic SQL (EXECUTE) as condition for IF statement

CodePudding user response：

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.