INSERT, UPDATE, DELETE, and returning data

Why this matters

Reading a table wrong gives you a wrong answer, which you notice. Writing to a table wrong destroys data, which you often do not notice until much later. Today you learn the three statements that change rows — INSERT, UPDATE, DELETE — and, just as importantly, the discipline that stops one careless line from rewriting every row you own.

By the end you can make a change and see exactly which rows it touched, in the same breath, rather than running a second query and hoping it describes the same rows.

INSERT: adding rows

INSERT names the table, the columns you are supplying, and the values.

INSERT INTO equipment (name, serial_number, installed_on)
VALUES ('Exhaust Fan 2', 'FAN-0002', '2023-04-17');

INSERT 0 1

The 1 is the number of rows inserted. (The 0 is a legacy field for an object id; ignore it.)

Always list the columns. INSERT INTO equipment VALUES (...) without a column list depends on the physical column order, so the day someone adds a column, every such statement quietly starts putting values in the wrong places. Columns you omit take their DEFAULT — which is how id, status, and created_at filled themselves in above.

RETURNING: see what you actually changed

PostgreSQL lets you attach RETURNING to INSERT, UPDATE, and DELETE. It hands back the rows as they now stand, in the same statement.

INSERT INTO equipment (name, serial_number, installed_on)
VALUES ('Exhaust Fan 2', 'FAN-0002', '2023-04-17')
RETURNING id, name, status;

 id |     name      |   status
----+---------------+-------------
  6 | Exhaust Fan 2 | operational
(1 row)

INSERT 0 1

That solves a real problem. The id was generated by the database, so before RETURNING existed the only way to learn it was a follow-up SELECT — which is both a second round trip and a guess, because between the two statements someone else may have inserted a row matching your WHERE. RETURNING is one statement, so there is no gap. Your Express route will use it on Day 49 to send back the created record.

RETURNING * gives every column; naming the columns you need is better for the same reason as SELECT.

UPDATE and the WHERE discipline

UPDATE sets new values on rows matching a condition.

UPDATE equipment
SET status = 'maintenance'
WHERE serial_number = 'CHL-0001'
RETURNING id, name, status;

 id |   name    |   status
----+-----------+-------------
  2 | Chiller 1 | maintenance
(1 row)

UPDATE 1

UPDATE 1 is your evidence: exactly one row changed. If it says UPDATE 4, you have just discovered a bug — while you can still remember what you did.

A statement that matches nothing is not an error:

UPDATE equipment SET status = 'retired' WHERE serial_number = 'NOPE-0000' RETURNING id;

 id
----
(0 rows)

UPDATE 0

UPDATE 0 is how an API knows to answer 404 Not Found rather than 200 OK. Silence is not success here — you must look.

-- Step 1: prove the condition selects what you think.
SELECT id, name, status FROM equipment WHERE serial_number = 'CHL-0001';
-- Step 2: same WHERE, now as an UPDATE.
UPDATE equipment SET status = 'maintenance' WHERE serial_number = 'CHL-0001';

DELETE

DELETE removes whole rows. It takes the same WHERE and the same RETURNING.

DELETE FROM equipment WHERE serial_number = 'FAN-0002' RETURNING id, name;

 id |     name
----+---------------
  6 | Exhaust Fan 2
(1 row)

DELETE 1

RETURNING on a DELETE is the only chance you get to record what the row contained — after the statement, it does not exist.

Remember yesterday's foreign key: maintenance_records.equipment_id was declared ON DELETE CASCADE, so deleting a piece of equipment also deletes its maintenance records. That is a deliberate choice, and it means one DELETE can remove far more rows than the count suggests. Where records must be kept, applications often prefer a soft delete — a deleted_at column set to the current time, with every query filtering WHERE deleted_at IS NULL — because a row that is only marked can be brought back.

Values are data, not SQL

Notice what has happened in every statement so far: you pasted a literal value straight into the SQL text. That is fine when you are typing it yourself and know what it says. It becomes dangerous the moment the value comes from a user, because a value glued into SQL text can stop being a value and start being SQL.

The fix is a parameterized query, sometimes called a prepared statement: you send the SQL structure once, with numbered placeholders $1, $2 where values go, and send the values separately. The database plans the statement from the structure alone, then fills the placeholders in as data. Nothing inside a value can change the shape of the statement.

You can see it in psql. PREPARE declares the structure; EXECUTE supplies values.

PREPARE find_by_serial (text) AS
  SELECT id, name, status FROM equipment WHERE serial_number = $1;

EXECUTE find_by_serial('PMP-0003');

 id |    name     |   status
----+-------------+-------------
  1 | Feed Pump 3 | operational
(1 row)

Now hand it a value engineered to be an attack:

EXECUTE find_by_serial('''; DROP TABLE equipment; --');

 id | name | status
----+------+--------
(0 rows)

Zero rows, and the table is untouched. The database searched for a piece of equipment whose serial number is literally the text '; DROP TABLE equipment; --, found none, and said so. The value never got to be SQL. That property is the entire defence against SQL injection, and on Day 49 you will wire it into your Express routes with the same $1 placeholders. It is worth understanding today, before there is a user typing into your API.

Walkthrough: one safe change, start to finish

A technician reports that Air Compressor A is back in service. Do it the careful way.

SELECT id, name, status FROM equipment WHERE serial_number = 'CMP-0011';

One row, currently maintenance. Same condition, now as the change:

UPDATE equipment
SET status = 'operational'
WHERE serial_number = 'CMP-0011'
RETURNING id, name, status;

 id |       name       |   status
----+------------------+-------------
  3 | Air Compressor A | operational
(1 row)

UPDATE 1

Three checks in one screen: the returned row is the one you meant, the new value is what you wanted, and the count is 1 rather than 5. Log the visit that caused the change:

INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes)
VALUES (3, '2025-06-02', 2.50, 'Valve plate reseated, returned to service')
RETURNING id, equipment_id, performed_on;

Two statements changed two tables, and right now nothing guarantees that both happened or neither. That gap is tomorrow's lesson.

Your turn

Produce a db/mutations.sql in which every UPDATE and DELETE carries an explicit condition.

1. Reload db/seed.sql so you start from known rows.
2. INSERT one new piece of equipment using RETURNING id, name, status. Write down the id the database chose.
3. INSERT a maintenance record for that equipment using the id you just got, with RETURNING.
4. Write the SELECT for "the equipment with serial number X", run it, then convert it to an UPDATE that sets status = 'maintenance'. Confirm UPDATE 1.
5. Run an UPDATE whose WHERE matches nothing and record the UPDATE 0.
6. DELETE the equipment you created in step 2, with RETURNING id, name. Then count the maintenance records for that id and explain the number you get. (ON DELETE CASCADE took them with it.)
7. PREPARE a statement with one $1 parameter, EXECUTE it with a normal value and then with '''; DROP TABLE equipment; --'. Record both results in a comment.
8. Add a comment above every statement in the file naming the rows it is meant to affect and the count you observed.

Common pitfalls

• Running UPDATE before checking its WHERE. Costs seconds to check, hours to recover.
• Trusting the row count without RETURNING. UPDATE 3 tells you how many, not which. RETURNING tells you which.
• Treating UPDATE 0 as success. No error was raised, and nothing happened. Check the count and map it to a 404 in your API.
• Building SQL by pasting values into a string. It works until a value contains a quote, and then it either errors or executes something you did not write. Placeholders always.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and read its chapters on updates and deletions.

1. The tutorial states plainly what happens to a DELETE with no WHERE. Find that sentence and copy it into db/mutations.sql as a comment.
2. This lesson claimed RETURNING works on all three mutating statements. Find where the documentation confirms it, and note whether it says anything about RETURNING on a DELETE that this lesson missed.

Tomorrow you stop looking at one table at a time and start answering questions that span two.