require-if-not-exists

What triggers it

Fires on a CreateStmt (CREATE TABLE) with if_not_exists unset on a non-temp table, or an IndexStmt (CREATE INDEX, including UNIQUE and CONCURRENTLY variants) with if_not_exists unset.

What does not

Temporary tables (CREATE TEMP TABLE) are skipped outright since they are ephemeral and do not need idempotency, and any CREATE TABLE/CREATE INDEX that already sets IF NOT EXISTS returns null immediately.

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 here — this is about migration idempotency. Re-running a CREATE TABLE/CREATE INDEX that already succeeded fails with a plain SQL error, not a locking problem.

Why it matters

Without IF NOT EXISTS, re-running a migration fails with "relation already exists". Idempotent migrations are safer for retry and rollback scenarios.

Unsafe, and safe

CREATE TABLE users (id BIGINT PRIMARY KEY);

CREATE TABLE IF NOT EXISTS users (id BIGINT PRIMARY KEY);

What it assumes

Assumes idempotent migrations are always wanted; some teams deliberately want a hard failure on re-run as a guard against applying the same migration twice, in which case this warning does not apply.

What the CLI prints

⚠ [MP023] WARNING (line 1)
  CREATE TABLE "users" without IF NOT EXISTS will fail if the table already exists. Use IF NOT EXISTS for idempotent migrations.

  Safe alternative:
  CREATE TABLE IF NOT EXISTS users (id BIGINT PRIMARY KEY)

  Why: Without IF NOT EXISTS, re-running a migration fails with "relation already exists". Idempotent migrations are safer for retry and rollback scenarios, and required by many deployment pipelines. Concurrent index builds are excluded on purpose: there, IF NOT EXISTS matches an index a failed build left INVALID and reports success without rebuilding it, so the retry-safe form is a preceding DROP INDEX CONCURRENTLY IF EXISTS instead (MP070).
  Docs: https://migrationpilot.dev/rules/mp023

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 MP023
CREATE TABLE users (id BIGINT PRIMARY KEY);

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

rules:
  MP023: false

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

• MP001require-concurrent-index-creationindexes should use CONCURRENTLY
• MP042require-index-nameindexes should have explicit names
• MP045require-primary-keytables should have a primary key