2

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?

1 Answer 1

4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.