Postgres by Example: UPDATE

UPDATE changes existing rows. The basic shape is UPDATE table SET column = value WHERE condition. Without a WHERE clause, every row in the table is updated — a mistake you only want to make in your own scratch database. Always check with a SELECT first, and consider wrapping risky updates in a BEGIN ... ROLLBACK until you are sure.

What you'll learn:

• Updating one column, multiple columns, and many rows at once
• Using WHERE to scope an update
• UPDATE ... FROM to update one table using data from another
• Returning the modified rows with RETURNING
• The transactional safety of UPDATE (and how to abuse it)

-- Single row, single column
UPDATE fruits SET name = 'apricot' WHERE id = 1;

-- Multiple columns at once
UPDATE fruits SET name = 'blueberry' WHERE id = 2;

-- Expression-based update: bump every id by 1000
-- (commented out so the rest of the lessons can find the original ids)
-- UPDATE fruits SET id = id + 1000;

-- UPDATE ... FROM: use another table as the source of values
DROP TABLE IF EXISTS price_changes_example;
CREATE TABLE price_changes_example (id integer, new_name text);
INSERT INTO price_changes_example VALUES (3, 'chestnut'), (4, 'damson');

UPDATE fruits AS f
SET    name = pc.new_name
FROM   price_changes_example pc
WHERE  f.id = pc.id;

DROP TABLE price_changes_example;

-- Returning the rows that changed
UPDATE fruits
SET    name = name        -- no-op, just for the demo
WHERE  id = 5
RETURNING id, name;

SELECT * FROM fruits ORDER BY id LIMIT 5;

UPDATE is fully transactional: until you COMMIT, no other session sees your changes (depending on isolation level), and a ROLLBACK undoes them. The same WHERE rules from SELECT apply.

UPDATE ... FROM is the PostgreSQL extension that lets you update rows in one table using data from another. The FROM clause introduces additional tables; the WHERE joins them. This is often clearer (and faster) than a correlated subquery inside the SET list.

The status message UPDATE n from psql tells you how many rows were modified. UPDATE 0 (zero rows) is a common signal that your WHERE did not match — useful to detect typos or stale assumptions in application code.

To run (requires fruits from earlier lessons):

$ psql -f source/update.sql postgres
UPDATE 1
UPDATE 1
CREATE TABLE
INSERT 0 2
UPDATE 2
DROP TABLE
 id |    name
----+------------
  5 | elderberry
(1 row)
UPDATE 1
 id |    name
----+------------
  1 | apricot
  2 | blueberry
  3 | chestnut
  4 | damson
  5 | elderberry
(5 rows)

Common pitfalls:

• Forgetting WHERE and updating the entire table — irreversible without a backup or a transaction. When in doubt, BEGIN; first, run the UPDATE, check the row count, COMMIT; or ROLLBACK;.
• Updating a primary key that is referenced by foreign keys: the ON UPDATE action of the FK decides whether the change propagates, fails, or sets NULL.
• Updating a column to the value it already has still rewrites the row, takes the lock, and triggers any BEFORE UPDATE trigger. Add WHERE col <> new_value (with NULL-safe comparison) if that matters.
• Long-running UPDATE on a hot table holds row locks; concurrent updates wait. Batch big updates into smaller transactions.

Tip: To safely preview an update, use a CTE: WITH affected AS (SELECT id FROM fruits WHERE ...) SELECT * FROM affected; first. Then swap the outer query for the UPDATE when you are sure.

Try it: Change one row in fruits to a different name. Use UPDATE fruits SET name = name || '!' WHERE id BETWEEN 1 AND 2 RETURNING *; to see which rows changed. Then run BEGIN; UPDATE fruits SET name = 'X'; SELECT * FROM fruits; ROLLBACK; and confirm the rollback discards the change.

Source: update.sql

Next: DELETE

Home: Postgres by Example