postgresql Modifying existing unique index

Question

I have this table on PostgreSQL:

example_dev=# \d products
                                    Table "public.products"
 Column |          Type          | Collation | Nullable |               Default                
--------+------------------------+-----------+----------+--------------------------------------
 id     | bigint                 |           | not null | nextval('products_id_seq'::regclass)
 name   | character varying(255) |           |          | 
 price  | integer                |           |          | 
 sku    | character varying(255) |           |          | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "name_price_unique_index" UNIQUE, btree (name, price)

As you see, I have a combined unique key name_price_unique_index on the columns name and price.

What I need to do is to modify this because I need to add sku column too. So I'd be needing a combination of the three columns.

Somthing like this:

"name_price_sku_unique_index" UNIQUE, btree (name, price, sku)

I've been looking for info on how to do this but I haven't found any.

Does anyone know how to do this?

Answer 1

You need to create a new index and replace the old one:

CREATE UNIQUE INDEX CONCURRENTLY newidx ON tab (name, price, sku);
DROP INDEX name_price_sku_unique_index;
ALTER INDEX newidx RENAME TO name_price_sku_unique_index;