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

Question

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!

Hey, thanks for your comment. I realised I explained myself very poorly, I updated the question. Sorry! — SeekanDestroy, Aug 26 '22 at 08:00

score 1 · Accepted Answer · answered Aug 26 '22 at 08:07

1

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))

answered Aug 26 '22 at 08:07

I was not familiar with `num_nulls(...)`. Thanks a_h_w_n_n – Richard Huxton Aug 26 '22 at 10:54

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

1 Answers1