Run UPDATE and DELETE in a Transaction First

An UPDATE or DELETE with a wrong or missing WHERE changes real rows, and PostgreSQL saves each statement the moment it finishes. A transaction gives you a way out. You open one, make the change, look at the result and then either keep it or throw it away. Do this every time on data you care about. It takes four extra lines.

Step 1: a table to practise on

CREATE TABLE products (
  product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  category   text NOT NULL,
  price      numeric(8,2) NOT NULL
);
INSERT INTO products (name, category, price) VALUES
  ('Notebook A5', 'stationery', 3.50),
  ('Gel Pen',     'stationery', 1.25),
  ('Desk Lamp',   'home',      18.00),
  ('Mug',         'home',       6.50);

CREATE TABLE
INSERT 0 4

Step 2: look before you change

Run the WHERE in a SELECT first. If these are not the rows you meant, stop.

SELECT product_id, name, price FROM products WHERE category = 'stationery';

 product_id |    name     | price
------------+-------------+-------
          1 | Notebook A5 |  3.50
          2 | Gel Pen     |  1.25
(2 rows)

Step 3: change it inside a transaction

BEGIN;
UPDATE products SET price = price * 2 WHERE category = 'stationery';
SELECT name, price FROM products ORDER BY product_id;

BEGIN
UPDATE 2
    name     | price
-------------+-------
 Notebook A5 |  7.00
 Gel Pen     |  2.50
 Desk Lamp   | 18.00
 Mug         |  6.50
(4 rows)

BEGIN opens the transaction. UPDATE 2 is the row count, and you expected two. Anyone else connected to the database still sees the old prices, and nothing is final yet. If the result is right, type COMMIT; and the change is saved. If it is not, type ROLLBACK;.

Step 4: undo a mistake

Now make the classic mistake: forget the WHERE.

ROLLBACK;
BEGIN;
DELETE FROM products;
SELECT count(*) AS rows_left FROM products;
ROLLBACK;
SELECT count(*) AS rows_left FROM products;

ROLLBACK
BEGIN
DELETE 4
 rows_left
-----------
         0
(1 row)

ROLLBACK
 rows_left
-----------
         4
(1 row)

The first ROLLBACK threw away the price change from Step 3. DELETE 4 is the warning: you meant to delete nothing, or one row, and it reports four. The DELETE removed every row, the count inside the transaction is 0, and after ROLLBACK the four rows are back. Without the transaction the table would be empty and the only way back would be a backup.

If you ever open a transaction and an error happens inside it, PostgreSQL marks the whole transaction as failed and refuses everything except ROLLBACK. That is annoying and safe.

Step 5: it covers more than rows

In PostgreSQL even table changes can be rolled back, which is not true everywhere.

BEGIN;
DROP TABLE products;
ROLLBACK;
SELECT count(*) AS rows_left FROM products;

BEGIN
DROP TABLE
ROLLBACK
 rows_left
-----------
         4
(1 row)

The DROP TABLE was undone. Other databases differ: MySQL, for one, saves a DROP TABLE immediately and cannot undo it. Do not rely on this outside PostgreSQL.

Related course: SQL for Beginners.