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

Hi,

a table in our database with about 3 million rows ended up in a state

where its seems to have duplicated entries (duplicated primary key values):

# \d some_table;

                                   Table "public.some_table"

 Column |            Type             |                        Modifiers

--------+-----------------------------+---------------------------------------------------------

 id     | integer                     | not null default nextval('some_table_id_seq'::regclass)

 field1 | character varying(40)       |

 field2 | character varying(128)      |

 ts     | timestamp without time zone |

Indexes:

    "some_table_pkey" PRIMARY KEY, btree (id)

    "ix_some_table_field1" btree (field1)

    "ix_some_table_field2" btree (field2)

    "ix_some_table_ts" btree (ts)

# select id, field1, field2 from some_table where field1 is null and field2 is not null;

   id    | field1 |            field2

---------+--------+----------------------------------

 2141750 |        | some_value2

(1 row)

# select id, field1, field2 from some_table where id = 2141750;

   id   | field1       |    field2

---------+-------------+----------------------------------

2141750 | some_value1   | some_value2

(1 row)

Another way this manifests itself it that running this:

# update some_table

   set field2 = field1

where

   id = 2141750;

works perfectly fine (but doesn't change the result of the first two queries above),

but this results in an error:

# update some_table

   set field2 = field1

where

   field1 is not null

   and field2 is null

   and ts between '2015-12-01' and '2015-12-02';

ERROR:  duplicate key value violates unique constraint "some_table_pkey"

DETAIL:  Key (id)=(2141750) already exists.

Do you have any idea what could be happening and what measures should be

undertaken to fix this issue? Thanks.