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 rule stays silent on the form Laravel's ->change() compiles to, where the type is restated beside the column's nullability on every call. $table->integer('stock')->default(0)->change() names integer whether the column was a bigint or already an integer, so it proves no truncation. PG.L2.TYPE_CHANGE_REWRITE reports that statement as undetermined instead.

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​

// Narrow through a second column instead of in place. Migration 1 — expand:
DB::statement('ALTER TABLE orders ADD COLUMN total_cents_int integer');

// Migration 2 — back-fill. A value that does not fit stops it here, before anything reads the column:
DB::statement('UPDATE orders SET total_cents_int = total_cents WHERE total_cents_int IS NULL');

// Migration 3, once every deployed reader and writer uses total_cents_int — contract, in a
// migration annotated #[SqlensAllowDestructive], because the drop is one:
DB::statement('ALTER TABLE orders DROP COLUMN total_cents');

When it has to happen in place​

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

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

Two migrations, because Laravel runs each in one transaction: a VALIDATE beside its NOT VALID would read the whole table under the add's ACCESS EXCLUSIVE lock.

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.