Changing data safely

Why changing data is different

Everything so far has been reading. A wrong SELECT costs you a confusing answer; run it again. A wrong UPDATE changes rows that other things depend on, and there's no "undo" button. In Lesson 5, a join on a name gave wrong numbers. An update on a name gives wrong data, kept until someone notices.

Think of a surgeon. Before operating: check the chart, mark the spot, count the instruments in. After: count them out again. None of that is the surgery; all of it is why the surgery goes right. Changing data has the same kind of routine, and this lesson is that routine:

1. Back up the table you're about to change.
2. Dry run: SELECT the rows your change will touch, and count them.
3. Change by key, inside a transaction.
4. Check the count matches the dry run, then commit.
5. Compare with the backup. Then add a rule so the problem can't come back.

It's CivicGrid's data routine, too: always dry-run, write by a stable key, keep a backup, and run the checks afterwards.

A backup first

The quickest backup is a copy of the table, made with CREATE TABLE ... AS:

CREATE TABLE leaders_backup AS SELECT * FROM leaders;
CREATE TABLE towns_backup AS SELECT * FROM towns;

SELECT 20
SELECT 12

Twenty leaders and twelve towns, copied: the rows, but none of the rules or keys. It's a snapshot to compare against and restore from, inside the same database. That makes it a good safety net for one change, and no use at all if the database is lost. Real backups are Lesson 13.

Transactions: try it, then decide

A transaction groups changes so they happen all together or not at all. BEGIN starts one. COMMIT keeps everything in it; ROLLBACK throws it all away, as if it never ran. Until you commit, other connections can't see your changes.

Use one to make the classic mistake, safely: an UPDATE that forgot its WHERE.

BEGIN;
UPDATE towns SET population = 0;

BEGIN
UPDATE 12

UPDATE 12: every town. UPDATE changes every row its WHERE matches, and with no WHERE, that's all of them. Look:

SELECT name, population FROM towns ORDER BY id LIMIT 3;

     name      | population 
---------------+------------
 Ashford       |          0
 Ashford       |          0
 Brennan Falls |          0
(3 rows)

Inside a transaction, it's only a draft. Throw it away:

ROLLBACK;
SELECT name, population FROM towns ORDER BY id LIMIT 3;

ROLLBACK
     name      | population 
---------------+------------
 Ashford       |      48210
 Ashford       |       1904
 Brennan Falls |      21544
(3 rows)

All back. Without the BEGIN, psql commits each statement the moment it runs, called autocommit, and the zeroes would be permanent. Start every change with BEGIN. The count after a change, UPDATE 12, is your first warning: it's the number to compare with your dry run.

By key, never by name

Thomas Reyes has left office, say, and his term needs an end date. Update him by name:

BEGIN;
UPDATE leaders SET ended = '2026-10-31' WHERE name = 'Thomas Reyes'
RETURNING id, town_id, name, ended;

 id | town_id |     name     |   ended    
----+---------+--------------+------------
  2 |       1 | Thomas Reyes | 2026-10-31
  4 |       2 | Thomas Reyes | 2026-10-31
(2 rows)

UPDATE 2

Two rows. Both Ashfords' mayors have left office now, though only one did. RETURNING shows exactly which rows a change touched, and here it caught the mistake before it was kept:

ROLLBACK;

It's the two Ashfords from Lesson 5 again, as a write this time. Names aren't unique, so change rows by their key: WHERE id = 2. Find the id with a SELECT first, which is the dry run.

The fixes

Now the county's problems, one at a time, each the same way.

Fix 1: the trailing space. Dry run: which names have spaces at either end?

SELECT id, name, length(name) FROM leaders WHERE name <> btrim(name);

 id |      name      | length 
----+----------------+--------
  8 | Sam Whitfield  |     14
(1 row)

One row, id 8. Change that row, by its id:

BEGIN;
UPDATE leaders SET name = btrim(name) WHERE id = 8 RETURNING id, name, length(name);

 id |     name      | length 
----+---------------+--------
  8 | Sam Whitfield |     13
(1 row)

UPDATE 1

UPDATE 1, as the dry run said, and 13 characters now. Keep it:

COMMIT;

Fix 2: Dunmore's two mayors. Owen Price took over in 2023, and Grace Lin's term was never closed:

SELECT id, name, started, ended FROM leaders
WHERE town_id = 5 AND role = 'mayor' AND ended IS NULL;

 id |    name    |  started   | ended 
----+------------+------------+-------
  9 | Grace Lin  | 2019-01-01 | 
 10 | Owen Price | 2023-01-01 | 
(2 rows)

Close hers on the day his began:

BEGIN;
UPDATE leaders SET ended = '2023-01-01' WHERE id = 9 RETURNING id, name, started, ended;
COMMIT;

 id |   name    |  started   |   ended    
----+-----------+------------+------------
  9 | Grace Lin | 2019-01-01 | 2023-01-01
(1 row)

UPDATE 1

Fix 3: Carl Webb's term. It starts in 2024 and ends in 2023. Which date is wrong can't be worked out from the data, so this needs a source. In real data, that means the town's own website or records. Here, say Linden's records show he started in 2023, and the year was a typo:

BEGIN;
UPDATE leaders SET started = '2023-01-01' WHERE id = 20 RETURNING id, name, started, ended;
COMMIT;

 id |   name    |  started   |   ended    
----+-----------+------------+------------
 20 | Carl Webb | 2023-01-01 | 2023-06-30
(1 row)

UPDATE 1

Fix 4: the phones. Dry run: which don't match the format? !~ means "doesn't match this pattern":

SELECT id, name, phone FROM towns WHERE phone !~ '^\(\d{3}\) \d{3}-\d{4}$';

 id |   name   |    phone     
----+----------+--------------
  7 | Fairview | 555-010-7700
(1 row)

Reformat it with regexp_replace: the brackets in the pattern capture the three groups of digits, and \1, \2 and \3 put them back in the new format. And Dunmore's phone, which it shared with Cedar Hollow, was simply wrong: say its website gives (555) 010-5500. Two changes, one transaction:

BEGIN;
UPDATE towns SET phone = regexp_replace(phone, '^(\d{3})-(\d{3})-(\d{4})$', '(\1) \2-\3')
WHERE id = 7 RETURNING id, name, phone;
UPDATE towns SET phone = '(555) 010-5500' WHERE id = 5 RETURNING id, name, phone;
COMMIT;

 id |   name   |     phone      
----+----------+----------------
  7 | Fairview | (555) 010-7700
(1 row)

UPDATE 1
 id |  name   |     phone      
----+---------+----------------
  5 | Dunmore | (555) 010-5500
(1 row)

UPDATE 1

And two problems left alone. Glen Oak and Juniper still have no current mayor. Nothing in the data says who it is, and making up a name to fill the gap is worse than a gap. An honest empty is a fact: "we don't know". That's how CivicGrid treats a city whose mayor it can't confirm.

When something fails inside a transaction

Two changes in one transaction: Cedar Hollow's website to https, and a population that isn't a number.

BEGIN;
UPDATE towns SET website = 'https://cedarhollow.larkspur.us' WHERE id = 4;
UPDATE towns SET population = 'unknown' WHERE id = 5;

BEGIN
UPDATE 1
ERROR:  invalid input syntax for type integer: "unknown"
LINE 1: UPDATE towns SET population = 'unknown' WHERE id = 5;
                                      ^

The first worked; the second failed. Now try anything at all:

SELECT name, website FROM towns WHERE id = 4;

ERROR:  current transaction is aborted, commands ignored until end of transaction block

After an error, a transaction refuses everything until you end it. That's the "all or nothing" promise being kept: half of it failed, so none of it can be kept, and Postgres won't let you carry on as if it hadn't. COMMIT here would roll back too. End it:

ROLLBACK;
SELECT name, website FROM towns WHERE id = 4;

ROLLBACK
     name     |            website             
--------------+--------------------------------
 Cedar Hollow | http://cedarhollow.larkspur.us
(1 row)

The website change, which worked, is gone too. That's the point: a group of changes that belong together, like moving money out of one account and into another, either all happens or none of it does.

INSERT, DELETE, and an upsert

INSERT adds a row, and DELETE removes the rows its WHERE matches. Everything above applies to DELETE too: no WHERE means every row, so dry-run it, and use a key.

INSERT INTO leaders (id, town_id, name, role, started)
VALUES (21, 8, 'Test Person', 'mayor', '2025-01-01') RETURNING id, town_id, name;
DELETE FROM leaders WHERE id = 21 RETURNING id, name;

 id | town_id |    name     
----+---------+-------------
 21 |       8 | Test Person
(1 row)

INSERT 0 1
 id |    name     
----+-------------
 21 | Test Person
(1 row)

DELETE 1

(The 0 in INSERT 0 1 is left over from older versions of Postgres, and always 0. The 1 is the number of rows.)

Often you have a row that might already be there: new figures for a town that may or may not exist yet. An upsert inserts it, or updates it if the key is already taken. In Postgres, that's ON CONFLICT:

INSERT INTO towns (id, name, kind, population) VALUES (6, 'Elk Ridge', 'village', 2750)
ON CONFLICT (id) DO UPDATE SET population = EXCLUDED.population
RETURNING id, name, population;

 id |   name    | population 
----+-----------+------------
  6 | Elk Ridge |       2750
(1 row)

INSERT 0 1

Town 6 already existed, so instead of an error, its population was updated. EXCLUDED means "the row you tried to insert". It still reports INSERT 0 1, so check RETURNING, or the data, to see which happened. Here's what it was:

SELECT id, name, population FROM towns_backup WHERE id = 6;

 id |   name    | population 
----+-----------+------------
  6 | Elk Ridge |       2710
(1 row)

Note what the upsert matches on: ON CONFLICT (id), a key. An upsert on a name would merge the two Ashfords, just like the update did.

Compare with the backup

Before you call it done, check what changed against what you meant to change. Join each table to its backup on the key, and keep the rows that differ:

SELECT l.id, b.name AS was, l.name AS now, b.started AS started_was, l.started,
       b.ended AS ended_was, l.ended
FROM leaders l JOIN leaders_backup b ON b.id = l.id
WHERE (l.name, l.started, l.ended) IS DISTINCT FROM (b.name, b.started, b.ended);

 id |      was       |      now      | started_was |  started   | ended_was  |   ended    
----+----------------+---------------+-------------+------------+------------+------------
  8 | Sam Whitfield  | Sam Whitfield | 2023-01-01  | 2023-01-01 |            | 
  9 | Grace Lin      | Grace Lin     | 2019-01-01  | 2019-01-01 |            | 2023-01-01
 20 | Carl Webb      | Carl Webb     | 2024-01-01  | 2023-01-01 | 2023-06-30 | 2023-06-30
(3 rows)

Exactly the three fixes, and nothing else. Row 8 looks unchanged, because the only change is the invisible space. Why IS DISTINCT FROM and not <>? Grace Lin's ended went from NULL to a date, and NULL <> '2023-01-01' is unknown, not true, so <> would have hidden her row. IS DISTINCT FROM treats NULL as a value that can differ. Lesson 8 has more of these.

SELECT t.id, b.phone AS was, t.phone AS now
FROM towns t JOIN towns_backup b ON b.id = t.id
WHERE t.phone IS DISTINCT FROM b.phone;

 id |      was       |      now       
----+----------------+----------------
  5 | (555) 010-4400 | (555) 010-5500
  7 | 555-010-7700   | (555) 010-7700
(2 rows)

The two phone fixes. (Elk Ridge's population changed too; this only compares phones.)

Now the rules go on

In Lesson 6, every one of these was refused, because the data broke them. Now it doesn't:

ALTER TABLE leaders ADD CONSTRAINT term_order CHECK (ended IS NULL OR ended >= started);
CREATE UNIQUE INDEX one_current_mayor ON leaders (town_id) WHERE role = 'mayor' AND ended IS NULL;
ALTER TABLE towns ADD CONSTRAINT one_phone_each UNIQUE (phone);
ALTER TABLE leaders ADD CONSTRAINT no_spaces CHECK (name = btrim(name));
ALTER TABLE towns ADD CONSTRAINT phone_format CHECK (phone ~ '^\(\d{3}\) \d{3}-\d{4}$');

ALTER TABLE
CREATE INDEX
ALTER TABLE
ALTER TABLE
ALTER TABLE

All five accepted. And they work: try to give Dunmore a second current mayor again.

INSERT INTO leaders (id, town_id, name, role, started) VALUES (22, 5, 'Grace Lin', 'mayor', '2026-01-01');

ERROR:  duplicate key value violates unique constraint "one_current_mayor"
DETAIL:  Key (town_id)=(5) already exists.

\d leaders shows them, with the names you gave them:

               Table "public.leaders"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 id      | integer |           | not null | 
 town_id | integer |           |          | 
 name    | text    |           |          | 
 role    | text    |           |          | 
 started | date    |           |          | 
 ended   | date    |           |          | 
Indexes:
    "leaders_pkey" PRIMARY KEY, btree (id)
    "one_current_mayor" UNIQUE, btree (town_id) WHERE role = 'mayor'::text AND ended IS NULL
Check constraints:
    "no_spaces" CHECK (name = btrim(name))
    "term_order" CHECK (ended IS NULL OR ended >= started)
Foreign-key constraints:
    "leaders_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

Find, fix, then forbid. Once you're sure, drop the backup copies:

DROP TABLE leaders_backup, towns_backup;

One thing the rules don't catch: Carl Webb's corrected term, early 2023, overlaps Joy Hart's, who has been Linden's mayor since 2022. Two mayors at once, in the past. No rule here compares one row's dates with another's. Lesson 10 writes that check as a query.

Spot the problem

Work out what's wrong before you read the explanation.

The cleanup that took a minute too long. Someone needs to delete one bad meeting. They type DELETE FROM meetings;, meaning to go back and add WHERE id = ... before the semicolon once they've looked the id up, and press Enter out of habit. What happened, and what would have saved them? The semicolon made it a complete statement, so psql sent it as it stood, with no WHERE, and with autocommit on, all 30 meetings were deleted and committed immediately. DELETE 30 was the only sign. A BEGIN first would have made it a draft, and ROLLBACK would have brought them back. A backup copy would have let them restore. Writing the WHERE first, and the DELETE FROM meetings in front of it last, avoids it entirely: a habit some people keep for exactly this reason.

Where you are now

You can change data with a way back at every step, and you've cleaned Larkspur County and locked the fixes in with rules. Lesson 8 goes back to the thing that's caught you in almost every lesson so far: NULL, and the other traps that look like ordinary values.

Check yourself

Key terms

• UPDATE, DELETE, INSERT: change, remove and add rows. UPDATE and DELETE affect every row their WHERE matches.
• RETURNING: shows the rows a change touched.
• Transaction: changes that happen all together or not at all, between BEGIN and COMMIT or ROLLBACK.
• Autocommit: psql's default, where each statement outside a transaction is kept immediately.
• Dry run: a SELECT of the rows a change will touch, run first.
• Upsert: insert, or update if the key already exists: ON CONFLICT ... DO UPDATE.
• IS DISTINCT FROM: "not equal", with NULL treated as a value.

Go deeper

• The PostgreSQL manual page Transactions (opens in a new tab).
• Try: give Cedar Hollow its https website, properly: dry run, BEGIN, change by id, check the count, COMMIT.
• Try: BEGIN;, then DELETE FROM meetings;, then SELECT count(*) FROM meetings;, then ROLLBACK; and count again. Then never do it without the BEGIN.
• To put every mistake back, reload: \q, then Lesson 1's load command.