INSERT, UPDATE and DELETE Without Regret

Reading data is safe. Changing it is not. INSERT adds rows, UPDATE changes them and DELETE removes them, and a mistake in any of the three is a mistake in your real data. The rule that keeps you out of trouble is short: look before you change, and be able to undo it.

INSERT: add rows

List the columns, then the values, in the same order. Anything you leave out gets its default or NULL.

INSERT INTO customers (name, email, city, joined)
VALUES ('Iris Lee', 'iris@example.com', 'Davao', '2026-10-01');

INSERT 0 1

INSERT 0 1 means one row was inserted. The leading 0 is a leftover from old PostgreSQL and is always zero now. Add several rows in one statement by listing more value groups, which is faster and all-or-nothing.

INSERT INTO customers (name, email, city, joined) VALUES
  ('Jon Uy', 'jon@example.com', 'Cebu', '2026-10-02'),
  ('Kai Bell', 'kai@example.com', NULL, '2026-10-03');

INSERT 0 2

PostgreSQL can hand back what it created with RETURNING, so you do not need a second query to learn the new id.

INSERT INTO customers (name, email, joined)
VALUES ('Lea Park', 'lea@example.com', '2026-10-04')
RETURNING customer_id, name;

 customer_id |   name
-------------+----------
          12 | Lea Park
(1 row)

INSERT 0 1

RETURNING is a PostgreSQL feature. Other databases differ: SQL Server calls it OUTPUT and MySQL has no direct equivalent. Constraints still apply. The next insert reuses an email and the whole statement is refused.

INSERT INTO customers (name, email, joined) VALUES ('Mia Ng', 'iris@example.com', '2026-10-05');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(iris@example.com) already exists.

UPDATE: change rows

UPDATE sets new values on the rows that match the WHERE. Check which rows match before you change them, by running the same WHERE in a SELECT.

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

     name     | price
--------------+-------
 Notebook A5  |  3.50
 Gel Pen      |  1.25
 Sticker Pack |  2.00
(3 rows)

UPDATE products SET price = price * 1.10 WHERE category = 'stationery';

UPDATE 3

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

     name     | price
--------------+-------
 Notebook A5  |  3.85
 Gel Pen      |  1.38
 Sticker Pack |  2.20
(3 rows)

UPDATE 3 matches the three rows you saw in the SELECT. The answer to "how many did it change?" is your safety check. If you expected 3 and see 3000, you have a problem you can still undo only if you used a transaction. The numeric column rounded 1.375 up to 1.38, because a numeric(8,2) keeps two decimals.

Now the mistake everyone makes once: an UPDATE without a WHERE changes every row. Do the dangerous version inside a transaction. BEGIN starts one, and ROLLBACK throws away everything since.

BEGIN;
UPDATE products SET price = 0;
SELECT name, price FROM products ORDER BY product_id LIMIT 3;
ROLLBACK;
SELECT name, price FROM products ORDER BY product_id LIMIT 3;

BEGIN
UPDATE 10
    name     | price
-------------+-------
 Notebook A5 |  0.00
 Gel Pen     |  0.00
 Desk Lamp   |  0.00
(3 rows)

ROLLBACK
    name     | price
-------------+-------
 Notebook A5 |  3.85
 Gel Pen     |  1.38
 Desk Lamp   | 18.00
(3 rows)

All ten prices became 0 inside the transaction, and the ROLLBACK put them back. Nothing else could see the zeros while the transaction was open. COMMIT would have kept them. PostgreSQL commits each statement on its own unless you open a transaction yourself, so a bare UPDATE with no WHERE is permanent the moment it finishes.

DELETE and the key that says no

DELETE removes the rows that match. Try to remove an order that has items.

DELETE FROM orders WHERE order_id = 4;

ERROR:  update or delete on table "orders" violates foreign key constraint "order_items_order_id_fkey" on table "order_items"
DETAIL:  Key (order_id)=(4) is still referenced from table "order_items".

The database refused. Order 4 still has a row in order_items, and a foreign key stops you from leaving an item that points at an order that no longer exists. Section 5 covers foreign keys. For now, the order of work is: remove the children, then the parent.

BEGIN;
DELETE FROM order_items WHERE order_id = 4;
DELETE FROM orders WHERE order_id = 4;
SELECT count(*) AS orders_left FROM orders;
ROLLBACK;
SELECT count(*) AS orders_left FROM orders;

BEGIN
DELETE 1
DELETE 1
 orders_left
-------------
          11
(1 row)

ROLLBACK
 orders_left
-------------
          12
(1 row)

Two DELETE statements, DELETE 1 for the item and DELETE 1 for the order, and 11 orders left. The ROLLBACK brought the 12th back. Delete rows that nobody references with a plain DELETE. Customer Iris has no orders yet.

DELETE FROM customers WHERE email = 'iris@example.com' RETURNING customer_id, name;

 customer_id |   name
-------------+----------
           9 | Iris Lee
(1 row)

DELETE 1

Common mistakes

• Running UPDATE or DELETE with no WHERE, or with a WHERE that matches more than you thought.
• Not reading the row count the statement prints.
• Testing a destructive statement on the real database instead of in a transaction.
• Deleting a row when "inactive" or "cancelled" would have kept the history.

If you skip this, one missing WHERE will teach you the lesson on real data. What would you check right now before running a DELETE on a table you did not create?