PostgreSQL check if column A is null then B must be null-CodePudding

I am new to PostgreSQL checks. I need to add a check constraint that specifies if column A is null, then B must be null But both can be NOT NULL

In probably more understandable words, if a row is inserted, both A and B columns has to be populated, there can't be a row with A populated if B is not populated, or the other way around. But, both CAN be null in the same row

Surprisingly I am struggling to find the answer to this. I was easily able to add constraints like A must be greater than B, or below 0, or both columns must be null (if that even makes sense), and more.

However, I can't find the answer and the correct syntax for this.

Any help here?

Thanks a lot!

CodePudding user response：

This can be achieved by using the handy function num_nulls(). You want that either both columns are not null (number of nulls = 0) or both are null (number of nulls = 2)

alter table the_table
   add constraint check_nulls_in_a_and_b
   check (num_nulls(a,b) in (0,2))