Adding a unique constraint on a nullable column in PSQL

Question

I am having a table with two columns. Want to add unique constraint on both columns. One of the column is nullable.

I m trying with this syntax:

ALTER TABLE a ADD CONSTRAINT a_unq UNIQUE (a_id, (coalesce(a_name, '')));

Its prompting error at or near "("

m confused, why are you using coalesce?? also if possible please confirm the db server you are using newer sql(2008+) server allows you to create Unique constraints with where clause which can be then used conditionally. — BlindSniper
– BlindSniper, Commented Dec 7, 2017 at 5:38
Create partial constraint(Refer - stackoverflow.com/questions/8289100/…) — learner
– learner, Commented Oct 9, 2019 at 11:44

D.Zotov · Accepted Answer · 2017-12-07 05:40:24Z

-3

You don't need to use coalesce in that case. Just create constraint in the usual way:

ALTER TABLE a ADD CONSTRAINT a_unq UNIQUE (a_id, a_name);

answered Dec 7, 2017 at 5:40

D.Zotov

2,3442 gold badges18 silver badges30 bronze badges

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

3 Comments

BlindSniper Over a year ago

actually one of his column is nullable that's why I think he tried to use coalesce. I think your query will not work in this scenario as it will not allow more that on null values

Laurenz Albe Over a year ago

Why do you think that the constraint will not allow more than one NULL value?

user330315 Over a year ago

@BlindSniper: if that was the case (which it isn't) then coalesce() would actually do the wrong thing as you replace null with '' which indeed means you can have only one combination of a_id and a null value. without coalesce you can add (1,null), (1,null) but with coalesce that would be turned into putting (1,'') into the index and that will only be allowed once

Collectives™ on Stack Overflow

Adding a unique constraint on a nullable column in PSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related