How is the table made?
create table market_post
(
.
.
.
d_id varchar(20) constraint unique_d_id unique,
.
.
.
);
create index market_post_d_i_219a22_idx on market_post (d_id, is_deleted);
It should be noted that above code is DDL of table and i created the indexes and unique constraint when the table was created and was full of data (ALTER....)
Sometimes it allows duplicate value in d_id
and sometimes it not allows!!
Let's test:
TEST1
SELECT id,d_id
FROM public.market_post
WHERE id in (1910764,2584556)
Result:
--------------------------------
| id | d_id |is_deleted|
--------------------------------
|1910764 | QYynk1fG | true |
--------------------------------
|2584556 | gYkgfj_M | true |
--------------------------------
now i want update:
UPDATE public.market_post SET d_id = 'gYkgfj_M'WHERE id = 1910764
Result:
[2022-07-24 10:31:52] 1 row affected in 116 ms
OMG! now result is:
---------------------
| id | d_id |
---------------------
|1910764 | gYkgfj_M |
---------------------
|2584556 | gYkgfj_M |
---------------------
interesting point
SELECT id,d_id FROM public.market_post WHERE d_id='gYkgfj_M'
only returnt one row !!!!!!!!
---------------------
| id | d_id |
---------------------
|1910764 | gYkgfj_M |
---------------------
TEST2
SELECT id,d_id
FROM public.market_post
WHERE id in (191076 , 258455)
Result:
--------------------------------
| id | d_id |is_deleted|
--------------------------------
|191076 | SYyFk1fA | false |
--------------------------------
|258455 | fYkDfjbb | false |
--------------------------------
now i want update:
UPDATE public.market_post SET d_id = 'fYkDfjbb' WHERE id = 191076
Result:
[23505] ERROR: duplicate key value violates unique constraint "unique_d_id"
Detail: Key (d_id)=(fYkDfjbb) already exists.
its guarantees that the duplicate value was not found in the rows where is_deleted=false
Unique constraint does not work in Postgres?(Of course it should work) Or has the index affected it?
is this bug? no , i tested it in new table (in my server and in SQL fiddle) and all of them work truly , and there isn't any bug
But the old table is not work
It should be noted that I created the indexes and unique constraint when the table was full of data
VAERSION:12
As @ErwinBrandstetter said in the comments, I should have rebuilt the indexes! Apparently, I had fallen into a Postgres bug
After running REINDEX TABLE market_post;
everything was solved
The hard part was where I had to distinguish values with duplicate IDs and delete one of them! As I said in the question, Postgres returns only one of the records. I took the entire table with the help of pandas and identified the duplicate values and then removed them
After the steps, it was time to REINDEX