---
title: "What a PostgreSQL column default does to existing rows | Proofline"
description: "Adding a column with a constant default is instant on PostgreSQL 11 and later. A volatile default such as gen_random_uuid() rewrites the table."
url: https://proofline.sh/learn/postgres-add-column-default/
---

[Learning center](/learn/)

# What a PostgreSQL column default does to existing rows

Adding a column with a constant default is instant on PostgreSQL 11 and later. A volatile default such as gen\_random\_uuid() rewrites the table. A stable default such as now() is instant, and it gives every existing row the same value: the time of the migration. Decide which value existing rows should hold before you choose the default.

Breaking migrations The Proofline team November 20, 2026 3 min read

## Add three columns to a table of 200,000 rows

An accounts table gets a plan, a creation time and a public identifier. We ran this on PostgreSQL 16.15 with `\timing on`, reading the table's file node after each statement to see whether PostgreSQL wrote a new copy of the table.

```
CREATE TABLE accounts (id integer PRIMARY KEY, email text NOT NULL);
INSERT INTO accounts
SELECT g, 'user' || g || '@example.com' FROM generate_series(1, 200000) g;

SELECT pg_relation_filenode('accounts') AS f0 \gset

ALTER TABLE accounts ADD COLUMN plan text NOT NULL DEFAULT 'free';
ALTER TABLE accounts ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();
ALTER TABLE accounts ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid();

SELECT pg_relation_filenode('accounts') = :f0 AS same_file;
```

| Column | Default | Time | Table rewritten |
| --- | --- | --- | --- |
| `plan` | `'free'` | 1.1 ms | No |
| `created_at` | `now()` | 1.0 ms | No |
| `public_id` | `gen_random_uuid()` | 406 ms | Yes |

## Why `gen_random_uuid()` rewrites and `now()` does not

PostgreSQL 11 stores a non-volatile default once in the catalog and returns it for every row written before the column existed, as the [ALTER TABLE documentation](https://www.postgresql.org/docs/16/sql-altertable.html) describes. A volatile function must give each row its own value, so PostgreSQL computes it row by row and writes a new table while holding an `ACCESS EXCLUSIVE` lock. On this small table that took 406 ms. The time grows with the table and its indexes, and reads wait the whole time.

`now()` is stable: it returns the start time of the current transaction. PostgreSQL evaluates it once and stores that one value.

```
SELECT count(DISTINCT created_at) AS distinct_created,
       count(DISTINCT public_id)  AS distinct_ids
FROM accounts;
```

| distinct\_created | distinct\_ids |
| --- | --- |
| 1 | 200000 |

All 200,000 existing accounts now report that they were created at the moment the migration ran. Nothing failed, and a report that counts sign-ups per day will show a spike on the deployment date. Each existing account did get its own UUID, at the cost of the rewrite.

## Choose the value for existing rows first

The default answers two separate questions: what new rows get when the application omits the column, and what existing rows get now. Ask the second one in the review.

-   When every existing row should hold the same value, a constant or stable default is correct and costs nothing.
-   When existing rows need their own values, as with identifiers, add the column as nullable without a default, set the default for new rows, and fill existing rows in batches. Then add `NOT NULL` without a long scan, as shown in [make SET NOT NULL skip the table scan](/learn/postgres-set-not-null-scan/).
-   When the true value is unknown, as with a creation time for old rows, keep the column nullable or derive the value from data you already have. Do not let the migration timestamp stand in for it.

```
ALTER TABLE accounts ADD COLUMN public_id uuid;
ALTER TABLE accounts ALTER COLUMN public_id SET DEFAULT gen_random_uuid();

UPDATE accounts SET public_id = gen_random_uuid()
WHERE id IN (
    SELECT id FROM accounts
    WHERE public_id IS NULL
    ORDER BY id
    LIMIT 10000
);
-- repeat until it updates 0 rows
```

## What to rehearse

Read the volatility of each function a default calls:

```
SELECT proname, provolatile FROM pg_proc
WHERE proname IN ('now', 'clock_timestamp', 'gen_random_uuid');
```

`v` means volatile and rewrites the table; `s` and `i` do not. A default that calls your own function inherits the volatility you declared for it. If the migration rewrites, time it on a copy at production size, or change it to the batched form above. Before any `ALTER TABLE`, also read [why a fast migration blocked every query](/learn/postgres-migration-lock-queue/), because even the 1 ms statements wait for their lock first.

Find the other changes that need a rehearsal in [which PostgreSQL migration needs a rehearsal](/learn/postgres-migration-rehearsal/).

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

-   ### [Make PostgreSQL SET NOT NULL skip the table scan](/learn/postgres-set-not-null-scan/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
    
-   ### [Which PostgreSQL column type changes rewrite the table](/learn/postgres-alter-column-type-rewrite/)
    
    Breaking migrations The Proofline team November 20, 2026 3 min read
