no-data-loss-type-narrowing

What triggers it

An AlterTableStmt with a command of subtype AT_AlterColumnType whose def.ColumnDef.typeName.names[].String.sval includes any of int2, smallint, int4, integer, float4, real. In practice only int2, int4 and float4 ever match, because the parser normalises the spelled-out names — TYPE integer arrives as [pg_catalog, int4]. The remaining three entries in the list are reachable only through a user-defined type that happens to be named integer, smallint or real.

What does not

Every narrowing that is not to one of those six names: TEXT to VARCHAR(50), NUMERIC(20,4) to NUMERIC(5,2), TIMESTAMP(6) to TIMESTAMP(0), BIGINT to NUMERIC. All are genuine data-loss narrowings and all pass clean, even though the doc uses TEXT to VARCHAR(50) as its own example. Also any ALTER TABLE without an AT_AlterColumnType command, and any AT_AlterColumnType whose def.ColumnDef.typeName.names is missing.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

ALTER COLUMN ... TYPE holds ACCESS EXCLUSIVE for the whole operation and rewrites every row of the table plus every index on it, so reads and writes are blocked for the full rewrite, not momentarily. Any query already running keeps the DDL waiting, and everything that arrives behind the DDL queues behind it too.

Why it matters

Narrowing a column type truncates or rejects values that exceed the new type bounds. This causes data loss and requires a full table rewrite under ACCESS EXCLUSIVE.

The operation, and the mitigation

ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- Verify no data exceeds new bounds first:
SELECT count(*) FROM users WHERE age > 32767;
-- Then alter with explicit cast
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

This operation is irreversible, so there is no syntax that makes it safe. The second block is what care looks like — and MigrationPilot still flags it. The mitigation is process: confirm nothing reads the object, keep a way back, and do it in a window where you can watch.

Deploy and transaction boundaries

There is no single-statement safe form, which is why the fixer classifies this plan-only rather than mechanical. The documented path is the MP007 expand-contract: prove no row overflows, add the narrower column, dual-write, backfill in batches, swap reads, then drop — spanning at least two deploys because the application has to be reading the new column before the old one goes.

What it assumes

The rule knows the destination type and nothing else. It cannot see the source type, so SMALLINT widened to INTEGER — safe, no possible overflow — is flagged exactly like BIGINT narrowed to INTEGER. It cannot see the data either: it has no idea whether any row exceeds the new range, and it never reads ctx.tableStats or any other production field, so passing --database-url does not sharpen it. The SELECT COUNT(*) pre-flight it suggests is something the operator has to run.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

⚠ [MP044] WARNING (line 1)
  Changing "age" on "users" to INT2 may cause data loss if existing values exceed the new type range. This also requires a full table rewrite.

  Safe alternative:
  -- Verify no data exceeds the new type range before changing:
  -- SELECT COUNT(*) FROM users WHERE "age" > <max_value_of_new_type>;
  -- Consider adding a CHECK constraint instead of narrowing the type.

  Why: Narrowing a column type (e.g., BIGINT → INT, TEXT → VARCHAR(50)) will fail if any existing row has a value that does not fit the new type. Even if it succeeds today, future inserts may fail unexpectedly. The change also requires a full table rewrite under ACCESS EXCLUSIVE lock.
  Docs: https://migrationpilot.dev/rules/mp044

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP044
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP044: false

# or keep it, and downgrade it
rules:
  MP044:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP007no-column-type-changeall type changes require table rewrites
• MP038prefer-bigint-over-intstart with BIGINT to avoid future narrowing
• MP030require-not-valid-checkuse CHECK constraints for validation