From: | still Learner <stilllearner23(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Primary and unique key corruption |
Date: | 2020-12-14 09:42:50 |
Message-ID: | CAB3gP9LECq+WN2KLHqG3Mk2wmt8bzWzcxKyfHU3SSfVw-0sipA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2020-12-14 09:48:28 | Re: Primary and unique key corruption |
Previous Message | Dischner, Anton | 2020-12-14 09:40:23 | AW: tools like innotop for PostgreSQL? |