PostgreSQL duplicate non-null values despite unique constraint

Question

I have a database with a unique constraint in a field that is apparently being ignored. That table also includes two rows with the same value for that field, but somehow querying by that value only returns one row.

koji=> select id, name, encode(name::bytea, 'hex') FROM package where id in (7694, 8429);
 id | name | encode 
------+-----------------------------+--------------------------------------------------------
 7694 | python-collectd_certificate | 707974686f6e2d636f6c6c656374645f6365727469666963617465
 8429 | python-collectd_certificate | 707974686f6e2d636f6c6c656374645f6365727469666963617465
(2 rows)
koji=> select * from package where name='python-collectd_certificate';
 id | name 
------+-----------------------------
 8429 | python-collectd_certificate
(1 row)
koji=> \d+ package
 Table "public.package"
 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id | integer | | not null | nextval('package_id_seq'::regclass) | plain | | 
 name | text | | not null | | extended | | 
Indexes:
 "package_pkey" PRIMARY KEY, btree (id)
 "package_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
...

I'm totally confused, what on earth is going on here? How is this even possible? Is this DB totally corrupted now?

• gsiems: Are they the same though? What does select distinct name from package where name like 'python%certificate' ; tell you?

Answer 1

That's a clear case of data corruption. Your index is broken, perhaps because you updated the C library. You will have to manually remove the duplicates, for example with

DELETE FROM package WHERE id = 7694;

Once all duplicates are removed,

REINDEX INDEX CONCURRENTLY package_name_key;

• Alex: You're probably right, but this is just ASCII text, so it couldn't have been any sort of ordering change? Should I start suspecting cosmic rays or aliens here?
• Laurenz Albe: Cosmic rays are a possibility, aliens are unlikely. It might be connected to the - signs.