postgresql - Postgres unique key with multiple null columns -


i'm running postgres 9.5 , trying create unique constraint based on 3 fields. problem i'm having 2 of columns can nullable rows these fields null not seen breach unique constraint. i'm aiming constraint trying update on conflict (upsert).

the table structure this

product_id integer not null colour text null size text null 

i found question here can along lines of following

create unique index idx_1 on table (product_id, colour, size) colour not null or size not null; create unique index idx_2 on table (product_id, colour, size) colour null or size null; 

i'm not sure if work having 2 fields in clause how can call unique index on conflict?

or maybe should approach different way?

if treating null empty string acceptable, can try with:

create unique index idx_1 on table (product_id, coalesce(colour,''), coalesce(size,''));  

Comments

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

java - No use of nillable="0" in SOAP Webservice -

ubuntu - Laravel 5.2 quickstart guide gives Not Found Error -