Re: Table with seemingly duplicated primary key values

Please. Always, ALWAYS, give the PostgreSQL version and O/S when reporting
a problem.

First, WHAT IS THE POSTGRESQL VERSION?????
WHAT IS THE O/S?????

Then try this:
select a.ctid, a.id, a.field1,
b.ctid, b.id, b.field1
from some_table a,
some_table b
WHERE a.ctid <> b.ctid
AND a.id = b.id;

On Tue, Dec 22, 2015 at 4:03 AM, Aleksander Łukasz <allllllx(at)gmail(dot)com>
wrote:

> 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.
>
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.