The following bug has been logged on the website:
Bug reference: 17361
Logged by: Роман Григорович
Email address: atzedus@gmail.com
PostgreSQL version: 12.7
Operating system: Ubuntu 12.7-0ubuntu0.20.04.1
Description:
I has a table in database with this structure, and this table have a unique
complex index named "parts_stock_stat_lower_stock_type_id_idx":
db=# \d parts_stock_stat
Table "public.parts_stock_stat"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as
identity
name | text | | not null |
qty | integer | | not null |
stock_type_id | integer | | not null |
Indexes:
"parts_stock_stat_pkey" PRIMARY KEY, btree (id)
"parts_stock_stat_lower_stock_type_id_idx" UNIQUE, btree (lower(name),
stock_type_id)
Following reindex command failed:
db=# reindex table parts_stock_stat;
ERROR: could not create unique index
"parts_stock_stat_lower_stock_type_id_idx"
DETAIL: Key (lower(name), stock_type_id)=(ha_hr/50, 4) is duplicated.
I was try to find problem rows with following:
db=# SELECT name::bytea, id, name, stock_type_id FROM parts_stock_stat WHERE
lower(name) = 'ha_hr/50' AND stock_type_id = 4;
name | id | name | stock_type_id
--------------------+-------+----------+---------------
\x48415f48522f3530 | 12442 | HA_HR/50 | 4
\x48415f48522f3530 | 14052 | HA_HR/50 | 4
(2 rows)
As a result it found 2 rows that is truly duplicates.
The unique index existed before the table was populated with data.
But how it can be possible with unique index described above?
Is this a bug? If needed, i can upload full binary copy of database
(~136Mb).
