Learning center

Why COALESCE(new, old) shows a value the user removed

Reading COALESCE(new_column, old_column) lets a new release serve rows before the backfill reaches them. It also brings back the old value whenever a user deliberately clears the new one, because the query cannot tell "not backfilled yet" from "set to nothing". Record which rows have been migrated in a separate column and read from that.

The fallback#

The customers table is moving from nickname to display_name. The backfill has reached customer 1. Release 2026.11.4 lets customers clear their display name, and Ben does. We ran this on PostgreSQL 16.15:

ALTER TABLE customers ADD COLUMN display_name text;
UPDATE customers SET display_name = nickname WHERE id = 1;    -- the backfill so far
UPDATE customers SET display_name = NULL WHERE id = 2;         -- Ben clears his display name

SELECT id, COALESCE(display_name, nickname) AS shown FROM customers ORDER BY id;
idshown
1Ana
2Ben
3

Ben removed his name, and the page still shows it. Nothing logged an error. If the name was removed for a reason the customer cares about, such as a former name, this is the result they complain about.

The same thing happens with any value that may legitimately be null in the new shape, or with a default that means "empty": a cleared phone number, an address line removed, a preference reset to "none".

Record migration separately#

Add a column that says the row's new value is authoritative, set it in the backfill and in every new-release write, and read from it:

ALTER TABLE customers ADD COLUMN display_name_set boolean NOT NULL DEFAULT false;

-- the backfill, and every 2026.11.4 write of display_name, sets it
UPDATE customers SET display_name_set = true WHERE id IN (1, 2);

SELECT id,
       CASE WHEN display_name_set THEN display_name ELSE nickname END AS shown
FROM customers ORDER BY id;
idshown
1Ana
2
3

Since PostgreSQL 11, adding a column with a constant default does not rewrite the table, as the ALTER TABLE notes explain, so the marker costs no more than a nullable column.

Rows the old release writes need the marker kept false, so the fallback keeps showing their old value. If the old release updates nickname on a migrated row, the trigger in use a trigger when the old release must keep filling the new column can copy it into display_name there.

When the fallback goes#

Once the backfill and a reconciliation show every row migrated, read display_name alone and drop the marker in a later release:

SELECT count(*) FROM customers WHERE NOT display_name_set;

Pass when the count is zero with the old release gone. A rollback to a release that reads nickname shows Ben's old name again; decide when to stop writing the old column covers that limit.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading