The effect of index on unique constraint columns

Question

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

Answer 1

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