Splitting a Column into Two with Expand-Contract

The customers.full_name column has served for years, and now marketing needs first and last names separately, the payments provider wants them as distinct fields, and the UI has two input boxes. Splitting one column into two looks like a data-cleaning script plus two ADD COLUMNs. In a live system it is a four-release migration, because at every moment some code writes only full_name (the old version, a batch import, an admin tool) and some code reads only the new columns — and the derivation from one to the other is lossy and ambiguous (“Mary Ann de la Cruz”). This guide applies Expand and Contract Methodology to a one-to-many column change: how to keep the representations consistent in both directions during the transition, how to handle rows the derivation cannot split cleanly, and when the old column can finally go.

Symptom / Error Signatures

A column split done in one step produces failures on both sides of the deploy:

• Old code inserting only full_name leaves first_name/last_name NULL, and new screens show blank names.
• New code writing only the new columns leaves full_name stale, and old code (emails, invoices, exports) prints outdated names.
• A NOT NULL added to the new columns too early fails inserts from old code: null value in column "first_name" violates not-null constraint.
• The backfill’s naive split (split_part(full_name, ' ', 1)) mangles multi-part names, and nobody can tell which rows were guessed.

Root Cause Analysis

The problem has two parts. The first is the familiar compatibility constraint: old and new code run concurrently, so the database must present both representations correctly to both for as long as both exist. The second is specific to splits: the mapping from old to new is not a function you can always trust. Composing a full name from parts is easy and deterministic; splitting a full name into parts is heuristic. That asymmetry shapes the plan — derive the new columns from the old with a heuristic while the old is authoritative, mark heuristic results so they can be reviewed or corrected, and switch authority to the new columns as soon as new code writes them, because from then on the reverse derivation is exact.

Immediate Mitigation

If a one-step split has already shipped and old code is leaving the new columns empty:

1. Add a trigger that fills whichever side is missing. It covers old writers (only full_name) and new writers (only parts) until the proper sequence is in place.

-- PostgreSQL · migration role · BEFORE trigger modifies the row being written
CREATE OR REPLACE FUNCTION customers_name_sync() RETURNS trigger AS $$
BEGIN
  IF NEW.first_name IS NULL AND NEW.full_name IS NOT NULL THEN
    NEW.first_name := split_part(NEW.full_name, ' ', 1);
    NEW.last_name  := nullif(substr(NEW.full_name, length(split_part(NEW.full_name, ' ', 1)) + 2), '');
    NEW.name_split_source := 'heuristic';
  ELSIF NEW.full_name IS NULL AND NEW.first_name IS NOT NULL THEN
    NEW.full_name := concat_ws(' ', NEW.first_name, NEW.last_name);
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
SET lock_timeout = '3s';
CREATE TRIGGER customers_name_sync BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION customers_name_sync();
-- ROLLBACK PATH: DROP TRIGGER customers_name_sync ON customers; DROP FUNCTION customers_name_sync();

2. Drop any premature NOT NULL on the new columns so old inserts succeed, and re-add it at the end of the sequence using the online pattern in adding NOT NULL via a CHECK constraint in Postgres.

3. Measure the damage. Count rows where the parts are missing or were split heuristically, so you know how much correction work the proper sequence must absorb.

-- PostgreSQL · read-only
SELECT count(*) FILTER (WHERE first_name IS NULL AND full_name IS NOT NULL) AS unsplit,
       count(*) FILTER (WHERE name_split_source = 'heuristic')              AS guessed,
       count(*) FILTER (WHERE full_name ~ '\S+\s+\S+\s+\S+')                AS three_or_more_tokens
FROM customers;

Permanent Fix / Long-Term Pattern

Release 1 — expand. Add first_name, last_name and a name_split_source flag, all nullable; install the sync trigger (split direction); backfill existing rows in batches, as in Backfill Optimization, flagging every heuristic split.

-- PostgreSQL · one backfill batch · repeated by a script advancing :lo
UPDATE customers
SET first_name = split_part(full_name, ' ', 1),
    last_name  = nullif(substr(full_name, length(split_part(full_name, ' ', 1)) + 2), ''),
    name_split_source = 'heuristic'
WHERE id >= :lo AND id < :lo + 5000 AND first_name IS NULL AND full_name IS NOT NULL;

Review flagged rows. Route ambiguous names (more than two tokens, particles such as “de”, “van”, “bin”) to a correction workflow — customer self-service or support — and mark corrected rows name_split_source = 'user'.

Release 2 — read new. New code reads the parts and writes both full_name and the parts; the trigger still covers old writers.

Release 3 — write new. Code writes only the parts. Replace the sync logic with the exact composing direction, so any remaining reader of full_name sees correct values. Verify through query statistics that nothing writes full_name.

Release 4 — contract. When nothing reads full_name, drop the trigger and the column, and add NOT NULL constraints if required. The contract rules are the same as in safely removing a NOT NULL column with expand-contract.

Verification Checklist

• The sync trigger was in place before the backfill started, so no row written during the backfill was missed.
• Every heuristically split row is flagged, and ambiguous cases have a correction path.
• Before reads switched, no row had full_name set and the parts NULL.
• After release 3, the trigger composes full_name exactly from the parts, and no writer sets full_name directly.
• full_name was dropped only after query statistics showed no reads for a full business cycle.

Frequently Asked Questions

Why not split the data once with a script and switch everything at once? Because old and new code overlap during every deploy, and some writers (imports, admin tools) change on their own schedules. Without a sync mechanism, whichever representation a writer ignores goes stale.

Is a trigger or application code better for keeping both columns in sync? A trigger covers every writer, including ones you do not control, which is why it is the default here. Application code can do the same if every writer is updated first, but that is exactly the assumption column splits tend to violate.

How should ambiguous names be handled? Flag them rather than guessing silently, and give users or support a way to correct them. After authority moves to the new columns, corrected values flow back into the composed full_name automatically.

Does the same approach work for other one-to-many splits? Yes — an address column split into street, city and postcode, or a price column split into amount and currency. The structure is identical; only the derivation logic changes.