PostgreSQL - add a constraint that is applied only when the columns are not NULL

Question

How can I create a UNIQUE constraint of multiple columns, that is applied only when the columns are not NULL? So assuming that I create a constraint for column a and b, 1 2 and 1 2 cannot co-exist but 1 NULL and 1 NULL can exist together.

C'mon, you could really have tried a simple standard unique constraint and found that it already does what you want. — sticky bit
– sticky bit, Commented Mar 18, 2021 at 2:14

Tim Biegeleisen · Accepted Answer · 2021-03-18 02:12:29Z

2

Actually, adding a unique constraint across the two columns would, by default, allow entering (1, NULL) twice. The reason for this is that NULL is treated as "unknown," and therefore Postgres will treat this as not being duplicate.

Check the demo to see this working in action.

answered Mar 18, 2021 at 2:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - add a constraint that is applied only when the columns are not NULL

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