prefer-text-over-varchar
VARCHAR(n) has no performance benefit over TEXT in PostgreSQL.
- operation
- Columns
- lock taken
- no table lock
- remediation
- Fixed by --fix
- category
- Types & schema style
What triggers it
Fires when a ColumnDef type name is varchar or character varying, checked across every column in a CreateStmt tableElts list and every AT_AddColumn command in an AlterTableStmt.
What does not
Non-VARCHAR column types pass straight through. For ALTER TABLE, only AT_AddColumn commands are inspected — ALTER COLUMN TYPE, DROP COLUMN, and other subcommands are ignored, so an existing VARCHAR column being altered some other way is not flagged by this rule.
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
No lock concern — TEXT and VARCHAR(n) use identical storage in PostgreSQL, so this is a schema-design preference, not a locking or performance issue.
Why it matters
In PostgreSQL, VARCHAR(n) and TEXT use the same storage. The length constraint adds overhead without benefit. Use TEXT with a CHECK constraint if you need length validation.
Unsafe, and safe
Flagged
ALTER TABLE users ADD COLUMN bio VARCHAR(500);
Safe alternative
ALTER TABLE users ADD COLUMN bio TEXT; -- If length validation needed: ALTER TABLE users ADD CONSTRAINT chk_bio_len CHECK (length(bio) <= 500) NOT VALID;
What it assumes
Assumes future length changes are likely; if the maximum length is genuinely fixed forever, such as a two-letter country code, VARCHAR(n) documents that intent and the future-rewrite concern never materializes.
What the CLI prints
⚠ [MP037] WARNING (line 1)
Column "bio" on "users" uses VARCHAR. In PostgreSQL, TEXT has identical performance. Use TEXT with a CHECK constraint if you need length validation.
Safe alternative:
-- Use TEXT instead of VARCHAR:
-- "bio" TEXT
-- If you need a max length, add a CHECK constraint:
-- "bio" TEXT CHECK (length("bio") <= <max_length>)
Why: In PostgreSQL, VARCHAR(n) and TEXT have identical performance: both use the same varlena storage. VARCHAR(n) only adds a length check constraint that makes future length changes require a table rewrite (on PG < 17) or at minimum a constraint adjustment. Use TEXT with a CHECK constraint if you need length validation.
Docs: https://migrationpilot.dev/rules/mp037Generated 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 MP037 ALTER TABLE users ADD COLUMN bio VARCHAR(500);
For the whole project, in .migrationpilotrc.yml — by name or by id:
rules:
MP037: false
# or keep it, and downgrade it
rules:
MP037:
severity: warningTry it
Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.
Run MP037 in the playground