Only one non-nullable value across 3 columns

Question

I have 3 "Nullable" columns.

I am looking for a way to restrain it so that:

1. One of the three has value, the other two columns must be null
2. All three cannot be null

Is it possible to do this?

I tried this:

CREATE UNIQUE INDEX alpha ON contacts ("a")
WHERE "b" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX beta ON contacts ("b")
WHERE "a" IS NULL AND "c" IS NULL;

CREATE UNIQUE INDEX gamma ON contacts ("c")
WHERE "a" IS NULL AND "b" IS NULL;

Answer 1

That's not what indexes are for, you need a check constraint for that,

For the "at most one can be not null" you can use a little trick: casting a boolean expression to an integer yields 0 or 1. If you add up the values for a is not null condition, the rule "at most one can be not null" translates to "the sum of those must always be 1":

alter table contacts add constraint only_one_not_null
   check (   (a is not null)::int 
           + (b is not null)::int 
           + (c is not null)::int = 1);

The above can be simplified by using the num_nonnulls() function:

alter table contacts add constraint only_one_not_null 
   check (num_nonnulls(a,b,c) = 1);

This also covers the "not all three can be null" requirement.

Answer 2

Here it is:

ALTER TABLE table ADD CONSTRAINT boa check (
("a" is null and "b" is null and "c" is not null) or 
("a" is null and "b" is not null and "c" is null) or 
("a" is not null and "b" is null and "c" is null))