I have a unique index name account_id_status_idx already created in PostgreSQL.
create unique index account_id_status_idx on customer (account_id, status);
I updated the table with a new column named shop_id. now account_id, status and shop_id also need to be unique.
create unique index account_id_status_shop_id_idx on customer (account_id, status, shop_id);
Now account_id to be optional. new rows violate the 1st index since null values.
Ex: (account_id=0, status=active,shop_id=1), (account_id=0, status=active,shop_id=2). account_id default should be 0.
So I need to update the 1st index as expected
alter index account_id_status_idx (account_id, status) where account_id!=0;
So. is it possible to update the index using ALTER without drop and re-create in PostgreSQL?