Hi Team,
The primary key seems corrupted on this table. I have a similar unique index issue with another table in the same database.
Also one more wired thing is the duplicity is shown only when the "ilike" command and not with the like or = command.
I will remove the duplicates and rebuild, but how come the issue happened in a couple of tables?
\d contactdtls
Table "xxxxx.contactdtls"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------+-----------+----------+---------
user_name | character varying(25) | | not null |
role_id | numeric(2,0) | | not null |
mobilenumber | numeric(10,0) | | |
emailid | character varying(40) | | |
Indexes:
"contactdtls_pkey" PRIMARY KEY, btree (user_name, role_id)
select count(1) from (select user_name,role_id,count(1) from contactdtls group by 1,2 having count(1)>1)as a;
count
-------
45
(1 row)
select user_name,role_id from contactdtls where user_name ilike 'AVR-01';
user_name | role_id
-----------+---------
AVR-01 | 1
AVR-01 | 1
(2 rows)
select user_name,role_id from contactdtls where user_name like 'AVR-01';
user_name | role_id
-----------+---------
AVR-01 | 1
(1 row)
select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit
(1 row)
Also, is there any other way to find all index corruptions of a database, in any stats table or so?