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