Postgres, duplicate unique index

Question

I have a unique index column named "hash"

However when I run query:

 SELECT *
 FROM urls_0
 WHERE hash = '\x0009cb31d8a6c0c64f6877c22a781804'::bytea

There are two rows and I can not reindex that index because of unique violation. What happen here. I'm using PostgreSQL 9.3.2

Are you 100% sure that this unique index exists for the table urls_0 solely on the column named hash? — Marcelo Zabani
– Marcelo Zabani, Commented Feb 16, 2014 at 13:55
Please, could you connect to the database, execute the following query and post the result here? SELECT indexrelid::regclass, indisunique, indisvalid FROM pg_catalog.pg_index WHERE indrelid = 'urls_0'::regclass; — MatheusOl
– MatheusOl, Commented Feb 16, 2014 at 15:43
@MatheusOl: The result is true all. But I have fixed my system by manual removed the duplicated row. — Truong Hua
– Truong Hua, Commented Feb 18, 2014 at 17:33

Borys · Accepted Answer · 2014-02-16 15:26:02Z

2

It could happen if you use CREATE INDEX CONCURRENTLY clause.

from documentation:

If a problem arises while scanning the table, such as 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.

edited Feb 16, 2014 at 15:26

user330315

answered Feb 16, 2014 at 15:19

Borys

3,0443 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Truong Hua Over a year ago

I think may be this is the right answer, but I do not use CREATE INDEX CONCURRENTLY. The index is created very first.

Collectives™ on Stack Overflow

Postgres, duplicate unique index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related