BUG #15952: UNIQUE CONSTRAINT does not fulfill its' purpose - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      15952
Logged by:          Neszt Tibor
Email address:      tibor@neszt.hu
PostgreSQL version: 9.6.14
Operating system:   Debian 8.11
Description:

Schema:

=> \d cimek
                                    Table "public.cimek"
     Column     |         Type          |                      Modifiers
----------------+-----------------------+-----------------------------------------------------
 kod            | bigint                | not null default
nextval('cimek_kod_seq'::regclass)
 irsz           | character varying(16) | not null default ''::character
varying
 telepules      | character varying(64) | not null default ''::character
varying
 telepulesresz  | character varying(64) | not null default ''::character
varying
 kozternev      | character varying(64) | not null default ''::character
varying
 kozterjelleg   | character varying(32) | not null default ''::character
varying
 hazszam        | character varying(64) | not null default ''::character
varying
 hazszambetujel | character varying(64) | not null default ''::character
varying
 hazszamvege    | character varying(64) | not null default ''::character
varying
 lepcsohaz      | character varying(32) | not null default ''::character
varying
 em             | character varying(32) | not null default ''::character
varying
 ajto           | character varying(64) | not null default ''::character
varying
 ajtobetujel    | character varying(64) | not null default ''::character
varying
 longitude      | character varying(32) | not null default ''::character
varying
 latitude       | character varying(32) | not null default ''::character
varying
Indexes:
    "cimek_pkey" PRIMARY KEY, btree (kod)
    "cimek_irsz_telepules_telepulesresz_kozternev_kozterjelleg_h_key" UNIQUE
CONSTRAINT, btree (irsz, telepules, telepulesresz, kozternev, kozterjelleg,
hazszam, hazszambetujel, hazszamvege, lepcsohaz, em, ajto, ajtobetujel)

Actual behavior:

=> select count(*), min(kod), max(kod), irsz, telepules, telepulesresz,
kozternev, kozterjelleg, hazszam, hazszambetujel, hazszamvege, lepcsohaz,
em, ajto, ajtobetujel from cimek group by irsz, telepules, telepulesresz,
kozternev, kozterjelleg, hazszam, hazszambetujel, hazszamvege, lepcsohaz,
em, ajto, ajtobetujel having count(*) > 1;
 count |  min  |  max  | irsz | telepules | telepulesresz | kozternev |
kozterjelleg | hazszam | hazszambetujel | hazszamvege | lepcsohaz | em |
ajto | ajtobetujel

-------+-------+-------+------+-----------+---------------+-----------+--------------+---------+----------------+-------------+-----------+----+------+-------------
     2 | 51611 | 55269 | 2092 | Budakeszi |               |  Foo      | utca
        | 46      |                |             |           |    |      |
(1 row)

The main problem is that there are two records with the same data set.

The real street name was altered with 'Foo' in this bugreport.

I tried to reproduce the issue locally without success. I encountered this
bug in multiple databases with different table and data. I could upgrade the
database to version 11 with "pg_upgradecluster 11 main -m upgrade -k", and
the issue still remain. To make sure it's not a human error, I tried to dump
the data and restore it. I got duplicate key error as expected.