---
title: "Why COALESCE(new, old) shows a value the user removed | Proofline"
description: "Reading COALESCE(new_column, old_column) lets a new release serve rows before the backfill reaches them."
url: https://proofline.sh/learn/coalesce-new-old-column-fallback/
---

[Learning center](/learn/)

# 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.

Schema conflicts The Proofline team November 22, 2026 2 min read

## 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;
```

| id | shown |
| --- | --- |
| 1 | Ana |
| 2 | Ben |
| 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;
```

| id | shown |
| --- | --- |
| 1 | Ana |
| 2 |  |
| 3 |  |

Since PostgreSQL 11, adding a column with a constant default does not rewrite the table, as the [ALTER TABLE notes](https://www.postgresql.org/docs/16/sql-altertable.html#SQL-ALTERTABLE-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](/learn/postgres-sync-trigger-old-writer/) 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](/learn/when-to-stop-dual-writing/) 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Use a trigger when the old release must keep filling the new column](/learn/postgres-sync-trigger-old-writer/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
    
-   ### [Decide when to stop writing the old column](/learn/when-to-stop-dual-writing/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
