Rule Reference

pg-migration-lint ships with 52 lint rules across seven categories:

How to use

Run pg-migration-lint --explain PGM001 to see the full explanation for any rule from the CLI.

Suppression

Rules can be suppressed inline with SQL comments:

-- Suppress a single statement:
-- pgm-lint:suppress PGM001
CREATE INDEX idx_foo ON bar (col);

-- Suppress an entire file (must appear before any SQL statements):
-- pgm-lint:suppress-file PGM001,PGM501

Severity levels

Severity Meaning
Critical Causes downtime, data corruption, or deploy failure. Must be fixed.
Major Performance issues or schema-integrity problems. Should be fixed.
Minor Potentially unintended behavior or style issues. Review recommended.
Info Informational — flags intentional but noteworthy operations.

0xx — Unsafe DDL Rules

PGM001 — Missing CONCURRENTLY on CREATE INDEX

Severity: Critical

Detects CREATE INDEX on an existing table without the CONCURRENTLY option. Without CONCURRENTLY, PostgreSQL acquires a SHARE lock for the entire duration of the index build, blocking all writes (inserts, updates, deletes) while allowing reads.

Example (bad):

CREATE INDEX idx_orders_status ON orders (status);

Fix:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

Does not fire when the table is created in the same set of changed files (locking an empty table is harmless). See also PGM003.

PGM002 — Missing CONCURRENTLY on DROP INDEX

Severity: Critical

Detects DROP INDEX without the CONCURRENTLY option, where the index belongs to a pre-existing table. Without CONCURRENTLY, PostgreSQL acquires an ACCESS EXCLUSIVE lock on the table.

Example (bad):

DROP INDEX idx_orders_status;

Fix:

DROP INDEX CONCURRENTLY idx_orders_status;

See also PGM003.

PGM003 — CONCURRENTLY inside transaction

Severity: Critical

Detects CREATE INDEX CONCURRENTLY or DROP INDEX CONCURRENTLY inside a migration unit that runs in a transaction. PostgreSQL does not allow concurrent index operations inside a transaction block — the command will fail at runtime.

Example (bad — Liquibase changeset with default runInTransaction):

<changeSet id="1" author="dev">
  <sql>CREATE INDEX CONCURRENTLY idx_foo ON bar (col);</sql>
</changeSet>

Fix:

<changeSet id="1" author="dev" runInTransaction="false">
  <sql>CREATE INDEX CONCURRENTLY idx_foo ON bar (col);</sql>
</changeSet>

See also PGM001 and PGM002.

PGM004 — DETACH PARTITION on existing table without CONCURRENTLY

Severity: Critical

Detects ALTER TABLE ... DETACH PARTITION on a pre-existing partitioned table without the CONCURRENTLY option. Plain DETACH PARTITION acquires ACCESS EXCLUSIVE on the parent and child, blocking all reads and writes for the duration. PostgreSQL 14+ supports DETACH PARTITION ... CONCURRENTLY, which uses a weaker lock.

Example:

ALTER TABLE measurements DETACH PARTITION measurements_2023;

Fix:

ALTER TABLE measurements DETACH PARTITION measurements_2023 CONCURRENTLY;

PGM005 — ATTACH PARTITION of existing table without pre-validated CHECK

Severity: Major

Detects ALTER TABLE ... ATTACH PARTITION where the child table already exists and has no CHECK constraint that references the partition key columns. Without a pre-validated CHECK constraint that implies the partition bound, PostgreSQL performs a full table scan under ACCESS EXCLUSIVE lock to verify every row.

Example:

ALTER TABLE measurements ATTACH PARTITION measurements_2024
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Recommended approach:

  1. Add a CHECK constraint mirroring the partition bound with NOT VALID.
  2. Validate the constraint separately (VALIDATE CONSTRAINT — allows concurrent reads/writes).
  3. Attach the partition (scan is skipped because the constraint is already validated).
ALTER TABLE measurements_2024 ADD CONSTRAINT measurements_2024_bound
    CHECK (ts >= '2024-01-01' AND ts < '2025-01-01') NOT VALID;
ALTER TABLE measurements_2024 VALIDATE CONSTRAINT measurements_2024_bound;
ALTER TABLE measurements ATTACH PARTITION measurements_2024
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

PGM006 — Volatile default on column

Severity: Minor

Detects ALTER TABLE ... ADD COLUMN with a volatile function call as the DEFAULT expression on an existing table. On PostgreSQL 11+, non-volatile defaults are applied lazily without rewriting the table. Volatile defaults (random(), gen_random_uuid(), clock_timestamp(), etc.) force a full table rewrite under an ACCESS EXCLUSIVE lock.

Note: now() and current_timestamp are STABLE in PostgreSQL, not volatile. They are evaluated once at ALTER TABLE time and the single value is stored in the catalog — no table rewrite occurs.

Severity levels per finding:

Example (flagged):

ALTER TABLE orders ADD COLUMN token uuid DEFAULT gen_random_uuid();

Fix:

ALTER TABLE orders ADD COLUMN token uuid;
-- Then backfill:
UPDATE orders SET token = gen_random_uuid() WHERE token IS NULL;

Does not fire on CREATE TABLE (no existing rows to rewrite).

PGM007 — ALTER COLUMN TYPE on existing table causes table rewrite

Severity: Critical

Detects ALTER TABLE ... ALTER COLUMN ... TYPE ... on pre-existing tables. Most type changes require a full table rewrite under an ACCESS EXCLUSIVE lock.

Safe casts (no finding):

Info cast: timestamp → timestamptz (safe in PG 9.2+ when timezone=UTC; verify your timezone config)

Example (bad):

ALTER TABLE orders ALTER COLUMN amount TYPE bigint;

Fix:

-- Create a new column, backfill, and swap:
ALTER TABLE orders ADD COLUMN amount_new bigint;
UPDATE orders SET amount_new = amount;
ALTER TABLE orders DROP COLUMN amount;
ALTER TABLE orders RENAME COLUMN amount_new TO amount;

PGM008 — ADD COLUMN NOT NULL without DEFAULT on existing table

Severity: Critical

Detects ALTER TABLE ... ADD COLUMN ... NOT NULL without a DEFAULT clause on a pre-existing table. This will fail immediately if the table has any rows.

Example (bad):

ALTER TABLE orders ADD COLUMN status text NOT NULL;

Fix (option A — add with default):

ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';

Fix (option B — add nullable, backfill, then constrain):

ALTER TABLE orders ADD COLUMN status text;
UPDATE orders SET status = 'pending' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

PGM009 — DROP COLUMN on existing table

Severity: Info

Detects ALTER TABLE ... DROP COLUMN on a pre-existing table. The DDL is cheap (PostgreSQL marks the column as dropped without rewriting), but the risk is application-level: queries referencing the dropped column will break.

Example:

ALTER TABLE orders DROP COLUMN legacy_status;

Recommended approach:

  1. Remove all application references to the column.
  2. Deploy the application change.
  3. Drop the column in a subsequent migration.

PGM010 — DROP COLUMN silently removes unique constraint

Severity: Minor

Detects ALTER TABLE ... DROP COLUMN where the dropped column participates in a UNIQUE constraint or unique index. PostgreSQL automatically drops dependent constraints, silently removing uniqueness guarantees.

Example (bad):

-- Table has UNIQUE(email)
ALTER TABLE users DROP COLUMN email;
-- The unique constraint is silently removed.

Fix: Verify that the uniqueness guarantee is no longer needed before dropping the column.

See also PGM011, PGM012.

PGM011 — DROP COLUMN silently removes primary key

Severity: Major

Detects ALTER TABLE ... DROP COLUMN where the dropped column participates in the table’s primary key. The table loses its row identity, affecting replication, ORMs, query planning, and data integrity.

Example (bad):

-- Table has PRIMARY KEY (id)
ALTER TABLE orders DROP COLUMN id;
-- The primary key is silently removed.

Fix: Add a new primary key on remaining columns before or after dropping the column.

See also PGM010, PGM012.

PGM012 — DROP COLUMN silently removes foreign key

Severity: Minor

Detects ALTER TABLE ... DROP COLUMN where the dropped column participates in a FOREIGN KEY constraint. The referential integrity guarantee is silently lost, potentially allowing orphaned rows.

Example (bad):

-- Table has FOREIGN KEY (customer_id) REFERENCES customers(id)
ALTER TABLE orders DROP COLUMN customer_id;
-- The foreign key constraint is silently removed.

Fix: Verify that the referential integrity guarantee is no longer needed before dropping the column.

See also PGM010, PGM011.

PGM013 — SET NOT NULL on existing table requires ACCESS EXCLUSIVE lock

Severity: Critical

Detects ALTER TABLE ... ALTER COLUMN ... SET NOT NULL on a pre-existing table. This acquires an ACCESS EXCLUSIVE lock and performs a full table scan to verify no existing rows contain NULL.

Example (bad):

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Fix (safe three-step pattern, PostgreSQL 12+):

-- Step 1: Add a CHECK constraint with NOT VALID (instant)
ALTER TABLE orders ADD CONSTRAINT orders_status_nn
  CHECK (status IS NOT NULL) NOT VALID;
-- Step 2: Validate (SHARE UPDATE EXCLUSIVE lock, concurrent reads OK)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;
-- Step 3: Set NOT NULL (instant in PG 12+ when CHECK is exactly `col IS NOT NULL`)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
-- Step 4 (optional): Drop the now-redundant CHECK
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

See also PGM015.

PGM014 — ADD FOREIGN KEY on existing table without NOT VALID

Severity: Critical

Detects ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY on a pre-existing table without the NOT VALID modifier. Without NOT VALID, PostgreSQL immediately validates all existing rows under a SHARE ROW EXCLUSIVE lock on both the referencing and the referenced table.

Example (bad):

ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

Fix (safe pattern):

ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_customer;

See also PGM015.

PGM015 — ADD CHECK on existing table without NOT VALID

Severity: Critical

Detects ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) on a pre-existing table without NOT VALID. Without NOT VALID, PostgreSQL acquires a SHARE ROW EXCLUSIVE lock and scans the entire table to verify all existing rows, blocking concurrent writes for the duration.

Example (bad):

ALTER TABLE orders ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'shipped', 'delivered'));

Fix (safe two-step pattern):

-- Step 1: Add with NOT VALID (instant, no scan)
ALTER TABLE orders ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'shipped', 'delivered')) NOT VALID;
-- Step 2: Validate (SHARE UPDATE EXCLUSIVE lock, concurrent reads OK)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_check;

See also PGM013, PGM014.

PGM016 — ADD PRIMARY KEY on existing table without USING INDEX

Severity: Major

Detects ALTER TABLE ... ADD PRIMARY KEY on an existing table that doesn’t use USING INDEX. Without USING INDEX, PostgreSQL builds a new index under ACCESS EXCLUSIVE lock, even if a matching unique index already exists.

Additionally, even with USING INDEX, if any PK columns are nullable, PostgreSQL implicitly runs SET NOT NULL under ACCESS EXCLUSIVE lock.

Example (bad):

ALTER TABLE orders ADD PRIMARY KEY (id);

Fix (safe pattern):

CREATE UNIQUE INDEX CONCURRENTLY idx_orders_pk ON orders (id);
ALTER TABLE orders ADD PRIMARY KEY USING INDEX idx_orders_pk;

If the PK columns are nullable, USING INDEX alone is not enough — PostgreSQL still runs an implicit SET NOT NULL (full table scan under ACCESS EXCLUSIVE). Make columns NOT NULL first using the safe CHECK-constraint pattern from PGM013:

-- Step 1: Make column NOT NULL safely (see PGM013)
ALTER TABLE orders ADD CONSTRAINT orders_id_nn
  CHECK (id IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_id_nn;
ALTER TABLE orders ALTER COLUMN id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_id_nn;
-- Step 2: Now USING INDEX is truly instant
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_pk ON orders (id);
ALTER TABLE orders ADD PRIMARY KEY USING INDEX idx_orders_pk;

PGM017 — ADD UNIQUE on existing table without USING INDEX

Severity: Critical

Detects ALTER TABLE ... ADD CONSTRAINT ... UNIQUE on an existing table without USING INDEX. Without USING INDEX, PostgreSQL builds a new unique index under ACCESS EXCLUSIVE lock. NOT VALID does not apply to UNIQUE constraints.

Example (bad):

ALTER TABLE orders ADD CONSTRAINT uq_email UNIQUE (email);

Fix (safe pattern):

CREATE UNIQUE INDEX CONCURRENTLY idx_orders_email ON orders (email);
ALTER TABLE orders ADD CONSTRAINT uq_email UNIQUE USING INDEX idx_orders_email;

See also PGM016.

PGM018 — CLUSTER on existing table

Severity: Critical

Detects a CLUSTER statement targeting a table that already exists in the database. CLUSTER rewrites the entire table and all its indexes in a new physical order, holding an ACCESS EXCLUSIVE lock for the full duration. Unlike VACUUM FULL, there is no online alternative. On large tables this causes complete unavailability for minutes to hours.

Example:

CLUSTER orders USING idx_orders_created_at;

Recommended approach:

  1. Schedule CLUSTER during a maintenance window when downtime is acceptable.
  2. Consider pg_repack or pg_squeeze for online table rewrites.
  3. For new tables, CLUSTER is fine — this rule only fires on existing tables.

PGM019 — ADD EXCLUDE constraint on existing table

Severity: Critical

Detects ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE (...) on a table that already exists. Adding an EXCLUDE constraint acquires an ACCESS EXCLUSIVE lock and scans all existing rows to verify the exclusion condition. Unlike CHECK and FOREIGN KEY constraints, PostgreSQL does not support NOT VALID for EXCLUDE constraints — there is no safe online path.

Example (bad):

ALTER TABLE reservations
  ADD CONSTRAINT excl_overlap
  EXCLUDE USING gist (room WITH =, period WITH &&);

Recommended approach:

  1. Schedule the migration during a maintenance window when downtime is acceptable.
  2. For new tables, adding EXCLUDE constraints in CREATE TABLE is fine — this rule only fires on existing tables.

PGM020 — DISABLE TRIGGER on table suppresses FK enforcement

Severity: Minor

Detects ALTER TABLE ... DISABLE TRIGGER (specific name, ALL, or USER) on any table. Fires at MINOR on existing tables and at INFO on all other tables (new or unknown). Since re-enables are not tracked, all tables are flagged to catch cases where triggers may be left disabled.

Example (bad):

ALTER TABLE orders DISABLE TRIGGER ALL;
INSERT INTO orders SELECT * FROM staging;

Fix (re-enable in the same migration):

ALTER TABLE orders DISABLE TRIGGER ALL;
INSERT INTO orders SELECT * FROM staging;
ALTER TABLE orders ENABLE TRIGGER ALL;

Recommended approach:

  1. Avoid disabling triggers in migrations entirely.
  2. If you must disable triggers for bulk data loading, ensure the DISABLE and ENABLE are in the same migration and wrapped in a transaction.
  3. On tables that are not pre-existing (new or unknown), this rule fires at INFO severity.

PGM021 — VACUUM FULL on existing table

Severity: Critical

Detects VACUUM FULL targeting an existing table. VACUUM FULL rewrites the entire table into a new data file under an ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration. On large tables this means minutes to hours of downtime.

Example (flagged):

VACUUM FULL orders;

Fix (use an online compaction tool):

pg_repack --table orders --no-superuser-check -d mydb

Or schedule during a maintenance window when downtime is acceptable.

PGM022 — Missing CONCURRENTLY on REINDEX

Severity: Critical

Detects REINDEX TABLE, REINDEX INDEX, REINDEX SCHEMA, REINDEX DATABASE, or REINDEX SYSTEM without the CONCURRENTLY option. Without CONCURRENTLY, REINDEX acquires an ACCESS EXCLUSIVE lock on the target table (or parent table for REINDEX INDEX), blocking all reads and writes for the duration of the rebuild.

Example (bad):

REINDEX TABLE orders;
REINDEX INDEX idx_orders_status;

Fix:

REINDEX TABLE CONCURRENTLY orders;
REINDEX INDEX CONCURRENTLY idx_orders_status;

The CONCURRENTLY option (PostgreSQL 12+) rebuilds the index without holding an exclusive lock for the entire operation. It takes longer but allows normal reads and writes to continue.

See also PGM003.

PGM023 — Multiple ALTER TABLE statements on the same table can be combined

Severity: Minor

Detects multiple separate ALTER TABLE statements targeting the same table within a single migration file, where all statements operate at the same lock level. Each separate statement acquires and releases the table lock independently, increasing the total lock contention window unnecessarily.

Example (bad — two lock acquisitions):

ALTER TABLE authors ALTER COLUMN name SET NOT NULL;
ALTER TABLE authors ALTER COLUMN email SET NOT NULL;

Fix (one lock acquisition):

ALTER TABLE authors
  ALTER COLUMN name SET NOT NULL,
  ALTER COLUMN email SET NOT NULL;

Chains are broken by any intervening statement that references the same table (e.g., CREATE INDEX CONCURRENTLY). Statements with different lock levels (e.g., VALIDATE CONSTRAINT vs SET NOT NULL) are tracked independently and do not trigger this rule against each other.

Tables created within the same set of changed files are exempt — lock contention for brand-new tables is harmless.

1xx — Type Anti-pattern Rules

These rules flag column types that should be avoided per the PostgreSQL wiki’s “Don’t Do This” recommendations.

PGM101 — Column uses timestamp without time zone

Severity: Minor

Detects columns declared as timestamp (which PostgreSQL interprets as timestamp without time zone). This type stores no timezone context, making values ambiguous across environments.

Example (bad):

CREATE TABLE events (created_at timestamp NOT NULL);

Fix:

CREATE TABLE events (created_at timestamptz NOT NULL);

PGM102 — Column uses timestamp or timestamptz with precision 0

Severity: Minor

Detects timestamp columns with precision 0. Precision 0 causes rounding, not truncation — a value of '23:59:59.9' rounds to the next day.

Example (bad):

CREATE TABLE events (created_at timestamptz(0));

Fix:

CREATE TABLE events (created_at timestamptz);

PGM103 — Column uses char(n) type

Severity: Minor

Detects columns declared as char(n) or character(n). In PostgreSQL, char(n) pads with trailing spaces, wastes storage, and is no faster than text or varchar.

Example (bad):

CREATE TABLE countries (code char(2) NOT NULL);

Fix:

CREATE TABLE countries (code text NOT NULL);
-- or: code varchar(2) NOT NULL

PGM104 — Column uses the money type

Severity: Minor

Detects columns declared as money. The money type formats output according to the lc_monetary locale setting, making it unreliable across environments and causing data corruption when moving data between servers.

Example (bad):

CREATE TABLE orders (total money NOT NULL);

Fix:

CREATE TABLE orders (total numeric(12,2) NOT NULL);

PGM105 — Column uses serial/bigserial instead of identity column

Severity: Info

Detects columns declared as serial, bigserial, or smallserial. Since PostgreSQL 10, identity columns (GENERATED ALWAYS AS IDENTITY) provide the same auto-incrementing behavior with tighter ownership, better permission handling, and SQL standard compliance.

Example (flagged):

CREATE TABLE orders (id serial PRIMARY KEY);

Fix:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

PGM106 — Column uses json type instead of jsonb

Severity: Minor

Detects columns declared as json. The json type stores exact input text and re-parses on every operation. jsonb stores a decomposed binary format that is faster, smaller, indexable (GIN), and supports containment operators.

Example (bad):

CREATE TABLE events (payload json NOT NULL);

Fix:

CREATE TABLE events (payload jsonb NOT NULL);

PGM107 — Primary key column uses integer or smallint instead of bigint

Severity: Major

Detects primary key columns that use integer (int4) or smallint (int2) instead of bigint (int8). High-write tables routinely exhaust the ~2.1 billion (integer) or ~32 000 (smallint) limit. Migrating to bigint later requires an ACCESS EXCLUSIVE lock and full table rewrite.

Example (flagged):

CREATE TABLE orders (id integer PRIMARY KEY);

Fix:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

PGM108 — Column uses varchar(n) instead of text

Severity: Info

Detects columns declared as varchar(n) or character varying(n). In PostgreSQL, varchar(n) has zero performance benefit over text — they share identical varlena storage. The length constraint adds an artificial limit that may require future schema changes.

Example (bad):

CREATE TABLE users (name varchar(100) NOT NULL);

Fix:

CREATE TABLE users (name text NOT NULL);
-- If validation is needed, use a CHECK constraint:
-- ALTER TABLE users ADD CONSTRAINT chk_name_len CHECK (length(name) <= 100) NOT VALID;
-- ALTER TABLE users VALIDATE CONSTRAINT chk_name_len;

PGM109 — Column uses floating-point type instead of numeric

Severity: Minor

Detects columns declared as real (float4), double precision (float8), or float. IEEE 754 floating-point types suffer from precision issues — for example, 0.1 + 0.2 ≠ 0.3. For money, quantities, measurements, or any domain where exact decimal values matter, numeric/decimal is the correct choice.

Example (bad):

CREATE TABLE products (price double precision NOT NULL);

Fix:

CREATE TABLE products (price numeric(10,2) NOT NULL);

2xx — Destructive Operation Rules

PGM201 — DROP TABLE on existing table

Severity: Minor

Detects DROP TABLE targeting a pre-existing table. The DDL is instant (no table scan or extended lock), so this is not a downtime risk — it is a data loss risk.

Example:

DROP TABLE orders;

Recommended approach:

  1. Ensure no application code, views, or foreign keys reference the table.
  2. Consider renaming the table first and waiting before dropping.
  3. Take a backup of the table data if it may be needed later.

PGM202 — DROP TABLE CASCADE on existing table

Severity: Major

Detects DROP TABLE ... CASCADE targeting a pre-existing table. CASCADE silently drops all dependent objects — foreign keys, views, triggers, and rules — that reference the dropped table. The developer may not be aware of all dependencies, leading to unexpected breakage.

A plain DROP TABLE (without CASCADE) would fail if dependencies exist, which is a safer default. CASCADE bypasses that safety net.

Example:

DROP TABLE customers CASCADE;
-- If 'orders' has a FK referencing 'customers', CASCADE silently drops that FK.

Recommended approach:

  1. Identify all dependent objects before dropping.
  2. Explicitly drop or alter dependencies in separate migration steps.
  3. Use plain DROP TABLE (without CASCADE) so PostgreSQL will error if unexpected dependencies remain.

PGM203 — TRUNCATE TABLE on existing table

Severity: Minor

Detects TRUNCATE TABLE targeting a pre-existing table. Unlike DELETE, TRUNCATE does not fire ON DELETE triggers, does not log individual row deletions, and cannot be filtered with a WHERE clause. The operation is irreversible once committed.

Example:

TRUNCATE TABLE audit_trail;

Recommended approach:

  1. Ensure the data is truly disposable or has been backed up.
  2. Consider whether ON DELETE triggers need to fire — if so, use DELETE.
  3. If truncating for a schema migration, document the intent clearly.

PGM204 — TRUNCATE TABLE CASCADE on existing table

Severity: Major

Detects TRUNCATE TABLE ... CASCADE targeting a pre-existing table. CASCADE silently extends the truncation to all tables that have foreign key references to the truncated table, and recursively to their dependents. The developer may not be aware of the full cascade chain, leading to unexpected data loss across multiple tables.

A plain TRUNCATE (without CASCADE) would fail if FK dependencies exist, which is a safer default.

Example:

TRUNCATE TABLE customers CASCADE;
-- If 'orders' has a FK referencing 'customers', CASCADE silently truncates 'orders' as well.

Recommended approach:

  1. Identify all dependent tables before truncating.
  2. Explicitly truncate each table in the correct order.
  3. Use plain TRUNCATE (without CASCADE) so PostgreSQL will error if unexpected dependencies remain.

PGM205 — DROP SCHEMA CASCADE

Severity: Critical

Detects DROP SCHEMA ... CASCADE. This is the most destructive single DDL statement in PostgreSQL — it silently drops every object in the schema: tables, views, sequences, functions, types, and indexes.

Unlike DROP TABLE CASCADE (which only removes objects that depend on one table), DROP SCHEMA CASCADE destroys the entire namespace and everything in it.

Example:

DROP SCHEMA myschema CASCADE;
-- Silently drops every table, view, function, sequence, and type in 'myschema'.

Recommended approach:

  1. Enumerate all objects in the schema before dropping.
  2. Explicitly drop or migrate each object in separate migration steps.
  3. Use plain DROP SCHEMA (without CASCADE) so PostgreSQL will error if the schema is non-empty.

3xx — DML in Migration Rules

PGM301 — INSERT INTO existing table in migration

Severity: Info

Detects INSERT INTO targeting a table that already exists in the database (not created in the same migration file). While seed data and lookup table population are valid use cases, they deserve review because large inserts can cause lock contention, WAL pressure, and timeouts.

Example (flagged):

INSERT INTO config (key, value) VALUES ('feature_x', 'enabled');

Not flagged when inserting into a table created in the same migration file.

PGM302 — UPDATE on existing table in migration

Severity: Minor

Detects UPDATE targeting a table that already exists in the database. On large tables, updates hold row locks for the full statement duration, generate WAL (spiking replication lag), and may time out under migration tool limits.

Example (flagged):

UPDATE orders SET status = 'pending' WHERE status IS NULL;

Recommended approach:

  1. Verify the row count is bounded (small lookup table = fine).
  2. For large tables, batch the update in chunks.
  3. Consider running the update outside the migration transaction.

PGM303 — DELETE FROM existing table in migration

Severity: Minor

Detects DELETE FROM targeting a table that already exists in the database. On large tables, deletes hold row locks, generate WAL, fire ON DELETE triggers, and produce dead tuples until autovacuum runs.

Example (flagged):

DELETE FROM audit_log WHERE created_at < '2020-01-01';

Recommended approach:

  1. Verify the row count is bounded.
  2. For large deletes, batch in chunks.
  3. If no triggers need to fire, consider TRUNCATE instead.

4xx — Idempotency Guard Rules

PGM401 — Missing IF EXISTS on DROP TABLE / DROP INDEX

Severity: Minor

Detects DROP TABLE or DROP INDEX without the IF EXISTS clause. Without IF EXISTS, the statement fails if the object does not exist, causing hard failures in migration pipelines that may be re-run.

Example (bad):

DROP TABLE orders;
DROP INDEX idx_orders_status;

Fix:

DROP TABLE IF EXISTS orders;
DROP INDEX IF EXISTS idx_orders_status;

PGM402 — Missing IF NOT EXISTS on CREATE TABLE / CREATE INDEX

Severity: Minor

Detects CREATE TABLE or CREATE INDEX without the IF NOT EXISTS clause. Without IF NOT EXISTS, the statement fails if the object already exists, causing hard failures in migration pipelines that may be re-run.

Example (bad):

CREATE TABLE orders (id bigint PRIMARY KEY);
CREATE INDEX idx_orders_status ON orders (status);

Fix:

CREATE TABLE IF NOT EXISTS orders (id bigint PRIMARY KEY);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);

See also PGM401.

PGM403 — CREATE TABLE IF NOT EXISTS for already-existing table is a misleading no-op

Severity: Minor

Detects CREATE TABLE IF NOT EXISTS targeting a table that already exists in the migration history. IF NOT EXISTS makes the statement a silent no-op — if the column definitions differ from the actual table state, the migration author may believe the table has the shape described in this statement, when in reality PostgreSQL ignores it entirely.

Example (bad):

-- V001: original table
CREATE TABLE orders (id bigint PRIMARY KEY);
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';

-- V010: redundant re-creation (silently ignored)
CREATE TABLE IF NOT EXISTS orders (
  id bigint PRIMARY KEY,
  status text NOT NULL DEFAULT 'pending',
  created_at timestamptz DEFAULT now()  -- this column will NOT be added
);

Fix: Remove the redundant CREATE TABLE IF NOT EXISTS. If the intent is to add columns, use ALTER TABLE ... ADD COLUMN instead.

5xx — Schema Design Rules

PGM501 — Foreign key without covering index on referencing columns

Severity: Major

Detects foreign key constraints where the referencing table has no index whose leading columns match the FK columns in order. Without such an index, deletes and updates on the referenced table cause sequential scans on the referencing table.

Example (bad):

ALTER TABLE order_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id);
-- No index on order_items(order_id)

Fix:

CREATE INDEX idx_order_items_order_id ON order_items (order_id);
ALTER TABLE order_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders(id);

Uses prefix matching: FK columns (a, b) are covered by index (a, b) or (a, b, c) but not by (b, a) or (a). Column order matters. The check uses the catalog state after the entire file is processed, so creating the index later in the same file avoids a false positive.

PGM502 — Table without primary key

Severity: Major

Detects CREATE TABLE (non-temporary) that results in a table without a primary key after the entire file is processed.

Example (bad):

CREATE TABLE events (event_type text, payload jsonb);

Fix:

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_type text,
  payload jsonb
);

Temporary tables are excluded. When PGM503 fires (UNIQUE NOT NULL substitute detected), PGM502 does not fire for the same table.

PGM503 — UNIQUE NOT NULL used instead of PRIMARY KEY

Severity: Info

Detects tables that have no primary key but have at least one UNIQUE constraint where all constituent columns are NOT NULL. This is functionally equivalent to a PK but less conventional.

Example (flagged):

CREATE TABLE users (
  email text NOT NULL UNIQUE,
  name text
);

Fix:

CREATE TABLE users (
  email text PRIMARY KEY,
  name text
);

When PGM503 fires, PGM502 does not fire for the same table.

PGM504 — RENAME TABLE on existing table

Severity: Info

Detects ALTER TABLE ... RENAME TO on a pre-existing table. Renaming breaks all queries, views, and functions referencing the old name. The rename itself is instant DDL (metadata-only), but downstream breakage can be severe.

Example (bad):

ALTER TABLE orders RENAME TO orders_archive;
-- All queries referencing 'orders' will fail.

Fix (backward-compatible):

ALTER TABLE orders RENAME TO orders_v2;
CREATE VIEW orders AS SELECT * FROM orders_v2;

Does not fire when a replacement table with the old name is created in the same migration unit (safe swap pattern).

PGM505 — RENAME COLUMN on existing table

Severity: Info

Detects ALTER TABLE ... RENAME COLUMN on a pre-existing table. A column rename silently invalidates all queries, views, and application code referencing the old name.

Example (bad):

ALTER TABLE orders RENAME COLUMN status TO order_status;
-- All queries using 'status' will fail with 'column does not exist'

Fix (multi-step approach):

  1. Add the new column.
  2. Backfill data from the old column.
  3. Update application code to use the new column.
  4. Drop the old column.

PGM506 — CREATE UNLOGGED TABLE

Severity: Info

Detects CREATE TABLE with the UNLOGGED keyword. Unlogged tables skip the write-ahead log for better write performance, but data is truncated after a crash and the table is not replicated to standby servers.

Example (flagged):

CREATE UNLOGGED TABLE scratch_data (id int, payload text);

When unlogged tables are appropriate:

PGM507 — DROP NOT NULL on existing table allows NULL values

Severity: Info

Detects ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL on tables that already exist. Dropping the NOT NULL constraint silently allows NULL values where application code may assume non-NULL.

Example (flagged):

ALTER TABLE orders ALTER COLUMN status DROP NOT NULL;

Why it matters:

Recommended approach:

  1. Verify that all application code paths handle NULLs in the column.
  2. Update aggregations and joins that assume non-NULL.
  3. Consider a CHECK constraint if only certain rows should allow NULL.

PGM508 — Duplicate or redundant index detected (prefix of another index)

Severity: Info

Detects CREATE INDEX where the new index’s columns are an exact duplicate or a leading prefix of another index on the same table. Redundant indexes waste disk space, slow writes, and add vacuum overhead.

Example (flagged):

-- Existing index: CREATE INDEX idx_orders_cust ON orders (customer_id);
CREATE INDEX idx_orders_cust_dup ON orders (customer_id);
-- Exact duplicate of idx_orders_cust.

CREATE INDEX idx_orders_cust_short ON orders (customer_id);
-- Redundant: idx_orders_cust_date ON (customer_id, created_at) covers this prefix.

Why it matters:

Does NOT fire when:

Fix: Drop the redundant index:

DROP INDEX CONCURRENTLY idx_orders_cust_short;

PGM509 — Mixed-case identifier or reserved word requires double-quoting

Severity: Info

Detects table and column names that require perpetual double-quoting — either because they contain uppercase characters or because they match a PostgreSQL reserved word.

Example (flagged):

CREATE TABLE "User" ("Id" bigint, "order" text);
-- Every query must now use: SELECT "Id", "order" FROM "User";

Why it matters:

Does NOT fire when:

Fix: Use a lowercase, non-reserved name:

CREATE TABLE users (id bigint, order_status text);

9xx — Meta-behavior Rules

PGM901 — Meta rules alter the behavior of other rules, they are not rules themselves

Severity: Info

Not a standalone lint rule. When a migration file is identified as a down migration, all findings from other rules are capped to INFO severity. Down migrations are informational only — they represent the undo path and are not expected to follow the same safety rules as forward migrations.

Detection is by filename suffix: the stem (filename minus .sql extension) must end with .down or _down. Examples:

Liquibase <rollback> blocks are not currently detected as down migrations.

This rule cannot be suppressed (it is applied automatically by the pipeline).

Quick reference table

Rule Severity Description
PGM001 Critical Missing CONCURRENTLY on CREATE INDEX
PGM002 Critical Missing CONCURRENTLY on DROP INDEX
PGM003 Critical CONCURRENTLY inside transaction
PGM004 Critical DETACH PARTITION on existing table without CONCURRENTLY
PGM005 Major ATTACH PARTITION of existing table without pre-validated CHECK
PGM006 Minor Volatile default on column
PGM007 Critical ALTER COLUMN TYPE on existing table causes table rewrite
PGM008 Critical ADD COLUMN NOT NULL without DEFAULT on existing table
PGM009 Info DROP COLUMN on existing table
PGM010 Minor DROP COLUMN silently removes unique constraint
PGM011 Major DROP COLUMN silently removes primary key
PGM012 Minor DROP COLUMN silently removes foreign key
PGM013 Critical SET NOT NULL on existing table requires ACCESS EXCLUSIVE lock
PGM014 Critical ADD FOREIGN KEY on existing table without NOT VALID
PGM015 Critical ADD CHECK on existing table without NOT VALID
PGM016 Major ADD PRIMARY KEY on existing table without USING INDEX
PGM017 Critical ADD UNIQUE on existing table without USING INDEX
PGM018 Critical CLUSTER on existing table
PGM019 Critical ADD EXCLUDE constraint on existing table
PGM020 Minor DISABLE TRIGGER on table suppresses FK enforcement
PGM021 Critical VACUUM FULL on existing table
PGM022 Critical Missing CONCURRENTLY on REINDEX
PGM023 Minor Multiple ALTER TABLE statements on the same table can be combined
PGM101 Minor Column uses timestamp without time zone
PGM102 Minor Column uses timestamp or timestamptz with precision 0
PGM103 Minor Column uses char(n) type
PGM104 Minor Column uses the money type
PGM105 Info Column uses serial/bigserial instead of identity column
PGM106 Minor Column uses json type instead of jsonb
PGM107 Major Primary key column uses integer or smallint instead of bigint
PGM108 Info Column uses varchar(n) instead of text
PGM109 Minor Column uses floating-point type instead of numeric
PGM201 Minor DROP TABLE on existing table
PGM202 Major DROP TABLE CASCADE on existing table
PGM203 Minor TRUNCATE TABLE on existing table
PGM204 Major TRUNCATE TABLE CASCADE on existing table
PGM205 Critical DROP SCHEMA CASCADE
PGM301 Info INSERT INTO existing table in migration
PGM302 Minor UPDATE on existing table in migration
PGM303 Minor DELETE FROM existing table in migration
PGM401 Minor Missing IF EXISTS on DROP TABLE / DROP INDEX
PGM402 Minor Missing IF NOT EXISTS on CREATE TABLE / CREATE INDEX
PGM403 Minor CREATE TABLE IF NOT EXISTS for already-existing table is a misleading no-op
PGM501 Major Foreign key without covering index on referencing columns
PGM502 Major Table without primary key
PGM503 Info UNIQUE NOT NULL used instead of PRIMARY KEY
PGM504 Info RENAME TABLE on existing table
PGM505 Info RENAME COLUMN on existing table
PGM506 Info CREATE UNLOGGED TABLE
PGM507 Info DROP NOT NULL on existing table allows NULL values
PGM508 Info Duplicate or redundant index detected (prefix of another index)
PGM509 Info Mixed-case identifier or reserved word requires double-quoting
PGM901 Info Meta rules alter the behavior of other rules, they are not rules themselves