Skip to main content

PG.L4.TYPE_NARROWING — Making a column smaller can lose what is in it

  • Category: safety
  • Level: 4
  • Confidence: heuristic
  • Downtime class: online
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

bigint → integer. double precision → real. varchar(255) → varchar(64). numeric(12,4) → numeric(8,2). Each one reduces the range or precision the column can hold, and each one has to do something about the rows that no longer fit.

What it does depends on the pair: an overflowing integer errors and aborts the migration; an over-long string or an over-precise number is truncated, quietly, and the original value is gone. The second outcome is the reason this is a rule.

The failure is worse in staging​

A narrowing change that passes in staging tells you nothing, because staging does not contain the one 2015 order with the eleven-digit total. The migration succeeds, ships, and the failure — or the silent truncation — happens on production data that never had a chance to be reviewed.

Flagged​

DB::statement('ALTER TABLE orders ALTER COLUMN total_cents TYPE integer');

Preferred​

// Widen, never narrow: a narrowing type change can fail or truncate on existing rows.
DB::statement('ALTER TABLE orders ALTER COLUMN total_cents TYPE bigint');

When you genuinely need to narrow​

Prove the data fits first, in its own deploy, and let the database do the proving:

ALTER TABLE orders ADD CONSTRAINT orders_total_fits
    CHECK (total_cents BETWEEN -2147483648 AND 2147483647) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_fits;

If the validation fails, you have found the rows — with no data lost and no deploy half-applied. Only after it passes is the type change a change of storage rather than a change of content.

And it still rewrites the table​

Narrowing is never binary-coercible, so PG.L2.TYPE_CHANGE_REWRITE applies to the same statement: this page is about what happens to the values, that one is about how long the table is locked while it happens. Both are worth reading before shipping one of these.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • expand_contract — Add the new shape, move readers and writers across, remove the old one — over three deploys.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.