ERROR: could not create unique index DETAIL: Key (id)=(3105115) is duplicated

Question

I am facing this problem on PostgreSQL, PgAdmin 4. I tried to alter one table.

ALTER TABLE mytable 
ALTER COLUMN some_column type NUMERIC;

But I keep getting error

DETAIL: Key (id)=(3105115) is duplicated.

But when I select that table Select count(id) from mytable where id = 3105115 I get count = 1.

Did anyone face this problem before?

Would be appreciated for any help.

Answer 1

You are likely experiencing data corruption.

Try to

REINDEX INDEX mytable_pkey;

or whatever the unique index is called. That will likely give you a similar error message.

You can find entries with duplicates with:

SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT id, count(*)
FROM mytable
GROUP BY id HAVING count(*) > 1;

After removing these duplicates, your operation should succeed.

As always with data corruption:

1. export the whole cluster and import it to a new cluster to get rid of any corruption.

2. check your hardware