How to make sure only one column is not null in postgresql table

Question

I'm trying to setup a table and add some constraints to it. I was planning on using partial indexes to add constraints to create some composite keys, but ran into the problem of handling NULL values. We have a situation where we want to make sure that in a table only one of two columns is populated for a given row, and that the populated value is unique. I'm trying to figure out how to do this, but I'm having a tough time. Perhaps something like this:

CREATE INDEX foo_idx_a ON foo (colA) WHERE colB is NULL
CREATE INDEX foo_idx_b ON foo (colB) WHERE colA is NULL

Would this work? Additionally, is there a good way to expand this to a larger number of columns?

Answer 1

Another way to write this constraint is to use the num_nonulls() function:

create table table_name 
(
  a integer, 
  b integer, 
  check ( num_nonnulls(a,b) = 1)
);

This is especially useful if you have more columns:

create table table_name 
(
  a integer, 
  b integer, 
  c integer,
  d integer,
  check ( num_nonnulls(a,b,c,d) = 1)
);

Answer 2

You can use the following check:

create table table_name 
(
  a integer, 
  b integer, 
  check ((a is null) != (b is null))
);

If there are more columns, you can use the trick with casting boolean to integer:

create table table_name 
(
  a integer, 
  b integer,
  ...
  n integer,
  check ((a is not null)::integer + (b is not null)::integer + ... + (n is not null)::integer = 1)
);

In this example only one column can be not null (it simply counts not null columns), but you can make it any number.