Creating indexes in PostgreSQL isn't always idempotent

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