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
Post a Comment