Postgres could not create unique index, key is duplicated

Viewed 32199

I'm trying to add a column to a table in my Postgres 9.3 database with this seemingly simple SQL:

ALTER TABLE quizzes ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT false;

However, I'm getting the following error:

ERROR:  could not create unique index "quizzes_pkey"
DETAIL:  Key (id)=(10557462) is duplicated.

Strangely enough, there are actually no rows with that id (which is the primary key, so it shouldn't have duplicates):

SELECT id FROM quizzes WHERE id = 10557462;
 id 
----
(0 rows)

In fact, it looks like that id has been skipped somehow:

SELECT id FROM quizzes WHERE id > 10557459 ORDER BY id LIMIT 4;
    id    
----------
 10557460
 10557461
 10557463
 10557464
(4 rows)

Why is this preventing me from adding a column, and how can I fix it?

2 Answers
Related