The following bug has been logged on the website:
Bug reference: 14940
Logged by: sergey frolov
Email address: sergey.frolov@smetarik.ru
PostgreSQL version: 9.6.6
Operating system: Windows 10, 64
Description:
Hi, I have noticed duplicated records inspite of primary key and unique
constraint.
select version ()
PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
The DDL is
CREATE TABLE nb.nb_basedtl
(
id integer NOT NULL,
base_id integer NOT NULL,
norm_id integer NOT NULL,
...
CONSTRAINT pk_nb_basedtl PRIMARY KEY (id),
CONSTRAINT unq_nb_basedtl UNIQUE (norm_id, base_id),
....
The problem is
select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id =
11658502 ;-- expected ONE row
(0,49);364507;0;11658502;269;46203
(0,49);370881;0;11658502;269;46203
(0,49);370882;0;11658502;269;46203
select (select count(1) from nb.nb_basedtl), (select count(1) from (select
id, count(1) from nb.nb_basedtl group by id having count(1) > 1 ) t )
3586895;50