Conditional Unique Contraints On Multiple Columns

Question

How to add a conditional unique constraint on table prod_name and prod_type columns of products table.

I would like to create constraint only when prod_type = 'LOCAL'

How could I do this?

Answer 1

If I understood right, you want this:

  create unique index products_name_ui 
    on products (case when prod_type = 'LOCAL' then prod_name else null end);