Search code examples
postgresqltransactionsplpgsql

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

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?

Solution

  • 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: