---
title: "Use a generated column as the first step of a column change | Proofline"
description: "When the new column is a pure function of the old one, a stored generated column fills existing rows and follows the old release's writes, with no backfill job."
url: https://proofline.sh/learn/postgres-generated-column-transition/
---

[Learning center](/learn/)

# Use a generated column as the first step of a column change

When the new column is a pure function of the old one, a stored generated column fills existing rows and follows the old release's writes, with no backfill job. It rewrites the table once, and the new release cannot write to it. When the new release needs to write the column, DROP EXPRESSION turns it into an ordinary column in about a millisecond. Install the sync trigger in the same transaction, or the old release's next writes leave it stale.

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

## Add it

The `orders` table has 1,000,000 rows. We ran this on PostgreSQL 16.15:

```
SELECT pg_relation_filenode('orders');   -- 16777
ALTER TABLE orders ADD COLUMN state text GENERATED ALWAYS AS (upper(status)) STORED;
-- Time: 911 ms
SELECT pg_relation_filenode('orders');   -- 16786
```

The file node changed, so PostgreSQL rewrote the table to store the computed values, holding an `ACCESS EXCLUSIVE` lock for the 911 ms. Every query on `orders` waited that long. At production size the wait grows with the table, so rehearse it on a copy; [why a fast migration blocked every query](/learn/postgres-migration-lock-queue/) shows the queue it forms.

From then on, the old release needs no changes:

```
INSERT INTO orders (status) VALUES ('paid');          -- 2026.11.3
UPDATE orders SET status = 'shipped' WHERE id = 1;     -- 2026.11.3
```

| id | status | state |
| --- | --- | --- |
| 1 | shipped | SHIPPED |
| 1000001 | paid | PAID |

## What it cannot do

The new release cannot write the column:

```
ERROR:  cannot insert a non-DEFAULT value into column "state"
ERROR:  column "state" can only be updated to DEFAULT
```

The expression can use only the same row and immutable functions. A lookup in another table fails with `cannot use subquery in column generation expression`, and `now()` fails with `generation expression is not immutable`. The [generated columns chapter](https://www.postgresql.org/docs/16/ddl-generated-columns.html) lists the restrictions. A conversion that depends on a mapping table, the session's time zone or application code needs a trigger instead.

## Hand the column to the new release

When the release that writes `state` is ready, convert the column:

```
ALTER TABLE orders ALTER COLUMN state DROP EXPRESSION;
-- Time: 1.4 ms, file node unchanged
```

The stored values stay, and the column becomes writable. It also stops following `status`:

```
UPDATE orders SET status = 'cancelled' WHERE id = 3;   -- 2026.11.3, after the drop
UPDATE orders SET state = 'SHIPPED' WHERE id = 4;       -- 2026.11.4
```

| id | status | state |
| --- | --- | --- |
| 3 | cancelled | PAID |
| 4 | paid | SHIPPED |

Order 3 shows the gap: the old release is still running, and its cancellation no longer reaches `state`. We repeated the step with the sync trigger from [use a trigger when the old release must keep filling the new column](/learn/postgres-sync-trigger-old-writer/) created in the same transaction:

```
BEGIN;
ALTER TABLE orders ALTER COLUMN state DROP EXPRESSION;
CREATE TRIGGER orders_fill_state BEFORE INSERT OR UPDATE OF status ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_fill_state();
COMMIT;
```

Order 3 then read `cancelled`, `CANCELLED`, order 4 kept the new release's `SHIPPED`, and a new old-release insert got `PAID`.

## The rollback limit

Before `DROP EXPRESSION`, rolling back means dropping the column. After it, `state` holds values the new release wrote, and the trigger keeps the old release's writes flowing, so the rollback limits are those of any sync trigger. Rehearse the `ADD COLUMN` rewrite at production size, and run the old release's write paths after the `DROP EXPRESSION` migration. Pass when `state IS DISTINCT FROM upper(status)` matches only rows the new release set deliberately. The other steps are in [moving data while two application releases share one database](/learn/data-migration-across-service-versions/).

## 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
    
-   ### [Why a fast PostgreSQL migration blocked every query](/learn/postgres-migration-lock-queue/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
