Search code examples
postgresqlindexingunique-index

Creating unique index seems to fail, but is created anyway?

Am creating a unique (multi-column) index, but it fails, because there non-unique rows in the table already. However, the index seems to be created anyway...

This is the SQL I'm using:

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS company_by_code ON public.company USING BTREE("owner","org","code")

Which results in the following response from Postgresql:

ERROR:  could not create unique index "company_by_code"
DETAIL:  Key (owner, org, code)=(ABC, DEF, XYZ) is duplicated.
SQL state: 23505

However, even though the CREATE statement seemed to error out, the INDEX seems to be created anyway.

Is that to be expected?

Solution

  • This is documented in the manual

    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. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID

    If you don't want that behaviour, don't use CONCURRENTLY