invalid-postgres-ddl · SQL

Why

Unsupported index options and constraint validation clauses fail when PostgreSQL executes a migration. These incompatibilities follow directly from the command and known built-in access-method capabilities.

Fix

Use B-tree for a unique index; use a supported multicolumn method for multiple keys; list only column names with a supported method in INCLUDE; qualify the indexed table instead of the new index name; and omit NOT VALID from UNIQUE or PRIMARY KEY additions. Preserve the intended uniqueness and query-access contract.

Examples

Before — flagged The table already fixes the index schema

CREATE INDEX app.item_key ON app.item (id);

After — preferred The table supplies the schema of its index

CREATE INDEX item_key ON app.item (id);

Autofix output

CREATE INDEX item_key ON app.item (id);

Before — flagged A built-in hash index cannot enforce uniqueness

CREATE UNIQUE INDEX item_key ON item USING hash (id);

After — preferred A B-tree index supports uniqueness and included columns

CREATE UNIQUE INDEX item_key ON app.item USING btree (id) INCLUDE (label);