UPDATE and DELETE

---

In PostgreSQL, DELETE and UPDATE are the "DML" (Data Manipulation Language) commands used to modify existing data. Because of the MVCC architecture we discussed earlier, these commands don't actually overwrite data—they create new versions of it.

---

The UPDATE Clause

The UPDATE statement modifies values in existing rows.

Standard Syntax

UPDATE users
SET is_active = true,
    updated_at = NOW()
WHERE user_id = '123e4567-e89b-12d3-a456-426614174000';

Advanced: UPDATE with a JOIN (The FROM Clause)

Often in ETL, you need to update a table based on values in another table (e.g., updating prices from a staging table). Postgres uses the FROM syntax for this.

UPDATE products p
SET price = s.new_price
FROM price_staging s
WHERE p.product_id = s.product_id;

---

The DELETE Clause

The DELETE statement removes rows from a table.

Standard Syntax

DELETE FROM logs
WHERE created_at < NOW() - INTERVAL '90 days';

The TRUNCATE Alternative

If you need to delete every single row in a table, DELETE is slow because it logs every row removal in the WAL. TRUNCATE is a "DDL" command that simply deallocates the data pages.

• DELETE: Slower, triggers run, can be filtered with WHERE.

• TRUNCATE: Near-instant, ignores triggers, cannot be filtered.

---

The RETURNING Clause (Postgres Power Feature)

A common requirement in Data Engineering is to know exactly what was changed. Postgres allows you to "pipe" the modified data out of the command immediately.

-- Update and get the old/new values back in one trip
UPDATE employees
SET salary = salary * 1.1
WHERE performance_score > 9
RETURNING employee_id, salary AS new_salary;

This is incredibly useful for logging changes or passing IDs to the next step in a pipeline without doing a second SELECT.

---

Under the Hood: The "Soft" Change

Remember our talk about Dead Tuples?

• UPDATE: Behind the scenes, Postgres marks the old row as "dead" and performs an INSERT of a new row with the updated values.

• DELETE: Marks the row as "dead" but does not reclaim the space immediately.

This is why frequent updates/deletes lead to Bloat, requiring the VACUUM process we covered earlier.

---

Summary Comparison

Feature	UPDATE	DELETE	TRUNCATE
Logic	Modifies columns.	Removes specific rows.	Wipes the whole table.
Speed	Medium.	Medium.	High.
WHERE clause?	Yes.	Yes.	No.
MVCC Impact	Creates 1 dead + 1 live tuple.	Creates 1 dead tuple.	Reclaims space instantly.
Locks	Row-level.	Row-level.	Table-level (Exclusive).

---