SELECT * and INSERT ... SELECT Broke Production at 3am

The 3am failure

At 03:12 the alert was a Postgres error, not a latency graph:

ERROR: null value in column "tenant_id" violates not-null constraint

The job was a backfill I wrote months earlier. Two statements, no column lists anywhere:

INSERT INTO accounts_archive
SELECT * FROM accounts
WHERE created_at < '2026-01-01';

When I wrote it, accounts and accounts_archive had the same columns in the same order. I checked with \d, saw them line up, and shipped it. Six months later a teammate added tenant_id uuid NOT NULL DEFAULT gen_random_uuid() and a generated email_domain column to accounts. Nobody changed accounts_archive. The migration kept running every night and kept writing rows.

Positional coupling, not name matching

INSERT INTO t SELECT * FROM s pairs the Nth column of the SELECT with the Nth column of the insert target. Postgres does not compare names. It compares types, and only where it must — integers and timestamps cast to text without complaint, and json accepts almost anything. So drift does not announce itself. Two things happen instead:

• When the counts still match but the order differs, the job writes values into the wrong columns and reports success.
• When the counts differ, you get INSERT has more expressions than target columns or the reverse, at whatever hour the job happens to run.

We had both. For a while created_at held email addresses and notes held timestamps, and the reports reading the archive were quietly wrong. Then tenant_id fell outside the select list and the job failed outright, which is how we found the earlier corruption.

Ordering is not something you can depend on either. A plain ALTER TABLE ... ADD COLUMN appends in Postgres, but MySQL's copying ALTER, CREATE TABLE ... AS SELECT, pg_dump and restore, and every "create new table, copy rows, rename" migration produce a new physical order. Drop a column and re-add it and it lands at the end.

Generated columns ride along in *

Postgres 12+ includes generated columns in SELECT *. MySQL's STORED and VIRTUAL columns do the same. That breaks positional inserts in two directions:

• A generated column added to the source table adds an expression the target never expected.
• A generated column on the target table means one * expression maps onto a column Postgres refuses to write, and you get cannot insert a non-DEFAULT value into column "email_domain".

Computed columns are the least visible member of this class, because they show up in the schema dump but not in anyone's mental model of the table.

The ORM model is not the schema

Our SQLAlchemy model listed columns in a different order than the database. That did not cause this bug, and that is the point: a model only shapes queries the ORM builds. A raw SQL string in a migration never reads it. The model also declares default=... and nullable=False, which is exactly how tenant_id NOT NULL DEFAULT ... reaches the table, so the ORM is often the source of the drift rather than the detector. Model drift stays invisible until something compares model metadata to information_schema.

CI checks that would have caught it

Ban positional inserts in migration files. This is the whole fix, and it is a short lint away:

# tests/test_migration_style.py
import pathlib, re

SELECT_STAR = re.compile(r"select\s+\*", re.IGNORECASE)

def test_migrations_name_their_columns():
    offenders = [
        f"{path}:{n}"
        for path in pathlib.Path("migrations").rglob("*.sql")
        for n, line in enumerate(path.read_text().splitlines(), 1)
        if SELECT_STAR.search(line)
    ]
    assert not offenders, offenders

Add these beside it:

• A contract test that loads information_schema.columns for the source and target tables and fails when the ordered name lists differ, unless the statement names every column explicitly.
• A CI job that applies all migrations to an empty database, runs the backfill twice, and asserts row counts and a checksum over the affected columns do not change.
• A model-versus-schema diff: alembic check, makemigrations --check --dry-run, or atlas schema diff against a database built only from migrations.
• Expand and contract for the new NOT NULL column: add it nullable, backfill, verify, then add the constraint. NOT NULL DEFAULT in one step is what turns wrong data into an outage.

The repair was mechanical once I saw it. Name the columns on both sides of every insert. Compare those two lists in a test. The next column someone adds will break that test instead of production.

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky