Postgres unique key with multiple null columns

Question

I'm running Postgres 9.5 and trying to create an unique constraint based on 3 fields. The problem I'm having is two of the columns can be nullable so rows with these fields as NULL are not seen to breach the unique constraint. I'm aiming for this to be a constraint as I am trying to do an update on conflict (UPSERT).

The table structure is something like this

product_id integer not null
colour text null
size text null

I found another question here where I can do something along the lines of the following

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

I'm not actually sure if this will actually work having two fields in the where clause but how can call this unique index on conflict?

Or maybe I should approach this a different way?

A unique key with nullable columns doesn't really make sense — user330315
– user330315, Commented Feb 5, 2016 at 21:17
@a_horse_with_no_name Maybe but what would a better way to achieve this be. Some products do not have a colour and some do not have a size but I don't want duplicates? — user4591756
– user4591756, Commented Feb 5, 2016 at 21:19
I don't see why the colour or size would be part of the primary key in the first place. Are you trying to model variants with this? — user330315
– user330315, Commented Feb 5, 2016 at 21:20
Yes, this is a separate table with all the product variants which relate to a product. Maybe I need a different or better approach? I don't really want two size 10's. — user4591756
– user4591756, Commented Feb 5, 2016 at 21:22
There might be a better solution, but when all else fails, you can write a SQL function that will check what you need and add it as a check constraint type. That's a valid way to handle complex logical constraints. — coladict
– coladict, Commented Feb 5, 2016 at 22:58

ikusimakusi · Accepted Answer · 2016-02-05 22:53:23Z

4

If treating null as empty string is acceptable, you can try with:

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

answered Feb 5, 2016 at 22:53

ikusimakusi

1598 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres unique key with multiple null columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related