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;

• gstackoverflow: why not removing index first ?
• Laurenz Albe: @gstackoverflow Your performance might suffer while there is no index. If that is irrelevant, then you might as well drop the index first.