Postgres constraint ensuring one column of many is present?

Since PostgreSQL 9.6 you have the num_nonnulls and num_nulls comparison functions that accept any number of VARIADIC arguments.

For example, this would make sure exactly one of the three columns is not null.

ALTER TABLE your_table
ADD CONSTRAINT chk_only_one_is_not_null CHECK (num_nonnulls(col1, col2, col3) = 1);

History & References

The PostgreSQL 9.6.0 Release Notes from 2016-09-29 say:

Add variadic functions num_nulls() and num_nonnulls() that count the number of their arguments that are null or non-null (Marko Tiikkaja)

On 2015-08-12, Marko Tiikkaja proposed this feature on the pgsql-hacker mailing list:

I'd like to suggest $SUBJECT for inclusion in Postgres 9.6. I'm sure everyone would've found it useful at some point in their lives, and the fact that it can't be properly implemented in any language other than C I think speaks for the fact that we as a project should provide it.

A quick and dirty proof of concept (patch attached):

=# select count_nulls(null::int, null::text, 17, 'bar');
  count_nulls
-------------
            2
(1 row)

Its natural habitat would be CHECK constraints, e.g:

  CHECK (count_nulls(a,b,c) IN (0, 3))

Avid code historians can follow more discussion from that point. :)

2021-09-17 update: As of today, gerardnll provides a better answer (the best IMO):

"Since PostgreSQL 9.6 you have the num_nonnulls and num_nulls comparison functions that accept any number of VARIADIC arguments."

In order to help people find the cleanest solution, I recommend you upvote gerardnll's answer.

(FYI, I'm the same person who asked the original question.)

---

Here is my original answer from 2013

Here is an elegant two column solution according to the "constraint -- one or the other column not null" PostgreSQL message board:

ALTER TABLE my_table ADD CONSTRAINT my_constraint CHECK (
  (column_1 IS NULL) != (column_2 IS NULL));

(But the above approach is not generalizable to three or more columns.)

If you have three or more columns, you can use the truth table approach illustrated by a_horse_with_no_name. However, I consider the following to be easier to maintain because you don't have to type out the logical combinations:

ALTER TABLE my_table
ADD CONSTRAINT my_constraint CHECK (
  (CASE WHEN column_1 IS NULL THEN 0 ELSE 1 END) +
  (CASE WHEN column_2 IS NULL THEN 0 ELSE 1 END) +
  (CASE WHEN column_3 IS NULL THEN 0 ELSE 1 END) = 1;

To compact this, it would be useful to create a custom function so that the CASE WHEN column_k IS NULL THEN 0 ELSE 1 END boilerplate could be removed, leaving something like:

(non_null_count(column_1) +
non_null_count(column_2) +
non_null_count(column_3)) = 1

That may be as compact as PSQL will allow (?). That said, I'd prefer to get to this kind of syntax if possible:

non_null_count(column_1, column_2, column_3) = 1