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?

Are they the same though? What does select distinct name from package where name like 'python%certificate' ; tell you? — gsiems
– gsiems, Commented Dec 9, 2022 at 14:46

Laurenz Albe · Accepted Answer · 2022-12-09 11:13:18Z

0

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;

answered Dec 9, 2022 at 11:13

Laurenz Albe

62.7k4 gold badges58 silver badges94 bronze badges

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?

Alex
– Alex

2022-12-09 11:29:29 +00:00
Commented Dec 9, 2022 at 11:29
Cosmic rays are a possibility, aliens are unlikely. It might be connected to the - signs.

Laurenz Albe
– Laurenz Albe

2022-12-09 11:43:27 +00:00
Commented Dec 9, 2022 at 11:43

Add a comment |

Stack Exchange Network

PostgreSQL duplicate non-null values despite unique constraint

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL duplicate non-null values despite unique constraint

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions