prefer-timestamptz

What triggers it

Two node shapes feed the same helper. A CreateStmt, over every tableElts[].ColumnDef; and an AlterTableStmt whose cmds[].AlterTableCmd.subtype is AT_AddColumn, over def.ColumnDef. The test is typeName.names[].String.sval containing timestamp and not containing timestamptz. Precision survives: TIMESTAMP(3) still parses to [pg_catalog, timestamp] and fires.

What does not

TIMESTAMPTZ and TIMESTAMP WITH TIME ZONE, both of which the parser lowers to timestamptz. ALTER TABLE ... ALTER COLUMN ... TYPE timestamp — only the AT_AddColumn subtype is read, so converting an existing column the wrong way is not caught. date, time, interval. CREATE TABLE AS and SELECT INTO, which are not CreateStmt. In the CreateStmt path a missing tableElts returns null for the whole check.

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

Nothing about locking. On CREATE TABLE the relation is new; on ADD COLUMN a bare type declaration with no default is a catalog-only change on PG 11+. The rule is a correctness rule about what the value means: timestamp stores a wall-clock reading with no zone attached, so the instant it referred to cannot be recovered once the server, the session TimeZone, or the writer moves.

Why it matters

TIMESTAMP WITHOUT TIME ZONE stores a raw datetime without timezone context. When your application or database server changes timezones, all values silently become wrong. Use TIMESTAMPTZ.

Unsafe, and safe

ALTER TABLE events ADD COLUMN created_at TIMESTAMP;

ALTER TABLE events ADD COLUMN created_at TIMESTAMPTZ;

What it assumes

It flags every timestamp column, including the cases where wall-clock time is the right model — a recurring local appointment, a business calendar date-time, a legally fixed local deadline. It has no way to ask what the column means. It also returns on the first offending column, so a table with six TIMESTAMP columns yields one violation, and it never inspects ALTER COLUMN ... TYPE, so a migration that converts timestamptz back down to timestamp passes clean.

What the CLI prints

⚠ [MP040] WARNING (line 1)
  Column "created_at" on "events" uses TIMESTAMP WITHOUT TIME ZONE. Use TIMESTAMPTZ to avoid timezone-related bugs.

  Safe alternative:
  -- Use TIMESTAMPTZ instead:
  -- "created_at" TIMESTAMPTZ

  Why: TIMESTAMP WITHOUT TIME ZONE stores the literal wall-clock time with no timezone context. When servers move between zones, or users are in different timezones, this causes silent data corruption. TIMESTAMPTZ stores instants in UTC and converts on display, which is almost always what you want.
  Docs: https://migrationpilot.dev/rules/mp040

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 MP040
ALTER TABLE events ADD COLUMN created_at TIMESTAMP;

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

rules:
  MP040: false

# or keep it, and downgrade it
rules:
  MP040:
    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

• MP037prefer-text-over-varcharchoose correct types from the start
• MP041ban-char-fieldavoid problematic types
• MP007no-column-type-changechanging types later is very expensive