Re: Table with seemingly duplicated primary key values - Mailing list pgsql-general

On 12/22/15 1:31 PM, Melvin Davidson wrote:

The fact that you have rows with an identical id of 2141750 verifies a
corrupted primary index.
To correct it, you need to decide which row to keep.

So review the results of
SELECT * FROM some_table WHERE ctid =  (79664,59) OR ctid = (79772,23)

DELETE FROM some_table
WHERE id = 2147150
     AND field1 = {value for field1 of row to delete}
     AND field2 = {value for field2 of row to delete}
...
...
    AND fieldn = {value for fieldn of row to delete};

Then
  REINDEX TABLE some_table;

And most importantly: review your logs for hardware errors and your Postgres and filesystem settings for anything dangerous. Index corruption is not normal and indicates the underlying hardware or OS is faulty (or maybe a bug in Postgres, but that's very unlikely).

You should also consider turning on page checksums if you haven't already.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com