Creating indexes in PostgreSQL isn't always idempotent

#todayilearned #postgres

Usually, when you call CREATE INDEX in Postgres, you expect the index to be created, or an error to be raised. However, if you are doing each of these:-

Creating a UNIQUE index
The data is not unique
Creating the index CONCURRENTLY

An index will be created, but be INVALID. From the documentation:-

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an “invalid” index.

Source