The split#
The people table holds names written by the old release. The backfill takes the first word as the given name and the rest as the family name. We ran this on PostgreSQL 16.15 with a fixture of fictional and public names:
UPDATE people
SET given_name = split_part(full_name, ' ', 1),
family_name = NULLIF(substr(full_name, length(split_part(full_name, ' ', 1)) + 2), '');
SELECT id, full_name, given_name, family_name,
concat_ws(' ', given_name, family_name) = full_name AS round_trips
FROM people ORDER BY id;
| Full name | Given | Family | Round trip |
|---|---|---|---|
| Ana Silva | Ana | Silva | t |
| Ludwig van Beethoven | Ludwig | van Beethoven | t |
| María José García López | María | José García López | t |
| Madonna | Madonna | t | |
| Jean-Luc␣␣Picard | Jean-Luc | ␣Picard | t |
| Pat O'Brien␣ | Pat | O'Brien␣ | t |
| 李 小龍 | 李 | 小龍 | t |
| Dr. Ada Lovelace | Dr. | Ada Lovelace | t |
| Ben Okafor | Ben | Okafor | t |
␣ marks a space the query kept. Every row round-trips because the split loses nothing: the parts are cut from the original at one position. That is the property a rollback needs. If the old release reads concat_ws(' ', given_name, family_name), or a trigger writes that value back into full_name, it gets back exactly what it stored.
What the round trip misses#
The parts are wrong for several rows. María José is a compound given name. 李 is a family name written first. "Dr." is a title. A query that flags the shapes a first-space split gets wrong turns that into a review list:
SELECT id, full_name,
CASE WHEN family_name IS NULL THEN 'no family name'
WHEN family_name ~ '^\s|\s\s|\s$' THEN 'extra whitespace'
WHEN given_name ~ '\.$' THEN 'title as given name'
WHEN family_name ~ '\s' THEN 'several words in family name'
WHEN full_name !~ '[A-Za-z]' THEN 'no Latin letters'
END AS needs_review
FROM people ORDER BY id;
Seven of the nine rows got a reason, and only Ana Silva and Ben Okafor came through clean. The flags are heuristics: "Ludwig van Beethoven" is split correctly and flagged anyway. On real data, count each reason and read a sample of each before deciding whether the new columns can be shown to people, or whether to ask customers to confirm their names.
When the new release edits the parts#
A correction written by the new release still round-trips:
UPDATE people SET given_name = 'María José', family_name = 'García López' WHERE id = 3;
SELECT concat_ws(' ', given_name, family_name) FROM people WHERE id = 3;
-- María José García López
A new release that trims spaces or normalizes case does not. Run the round-trip query after the new release's writes too, and count the rows whose old value changed. Those are the rows a rollback would show differently from what the customer last saw in the old release.
The checks#
Before switching readers: the review query's counts per reason, read and accepted. Before relying on a rollback: the round trip true for every row, including rows the new release wrote. Both checks belong in the pull request that switches readers. The rest of the sequence is in moving data while two application releases share one database.