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.