sql - Postgres could not create unique index, key is duplicated -

i'm trying add column table in postgres 9.3 database seemingly simple sql:

alter table quizzes add column deleted boolean not null default false; 

however, i'm getting following error:

error:  not create unique index "quizzes_pkey" detail:  key (id)=(10557462) duplicated. 

strangely enough, there no rows id (which primary key, shouldn't have duplicates):

select id quizzes id = 10557462;  id  ---- (0 rows) 

in fact, looks id has been skipped somehow:

select id quizzes id > 10557459 order id limit 4;     id     ----------  10557460  10557461  10557463  10557464 (4 rows) 

why preventing me adding column, , how can fix it?

i suspect have pre-existing index corruption or visibility issues.

when alter table ... add column ... default ... full table rewrite. rebuilds indexes, in process noticing problem on heap.

you'll find vacuum full on table produces same error.

i expect that

begin; set local enable_indexscan = off; set local enable_bitmapscan = off; set local enable_indexonlyscan = off;  select ctid,xmin,xmax,id quizzes id = 10557462; rollback; 

will reveal tuples exist.

please first read , act on this wiki page. once you've done that, check version. running or have ever run postgresql 9.3 version older 9.3.9? replica promoted? if so, explains due known multixact bugs fixed there:

• 9.3.7 relnotes
• 9.3.9 relnotes

otherwise, hard what's happening. it'd necessary take @ problem heap page(s) using pageinspect, @ pg_controldata output, , possibly @ b-tree pages referring heap pages.