BUG #15952: UNIQUE CONSTRAINT does not fulfill its' purpose

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: tibor(at)neszt(dot)hu
Subject: BUG #15952: UNIQUE CONSTRAINT does not fulfill its' purpose
Date: 2019-08-12 20:22:58
Message-ID: 15952-d777d2ea6569227a@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15952
Logged by: Neszt Tibor
Email address: tibor(at)neszt(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jeff Janes 2019-08-12 20:38:04 Re: BUG #15950: pg_freespace.avail is 0
Previous Message Daniel Adeniji 2019-08-12 19:48:21 Re: BUG #15951: http://www.postgresql.sk/support/submitbug not working