Postgres constraint ensuring one column of many is present?

Question

What are good ways to add a constraint to PostgreSQL to check that exactly one column (from a set of columns) contains a non-null value?

Update: It is likely that I want to use a check expression as detailed in Create Table and Alter Table.

Update: I'm looking through the available functions.

Update: Just for background, here is the Rails validation logic I'm currently using:

validate :multi_column_validation def multi_column_validation   n = 0   n += 1 if column_1   n += 1 if column_2   n += 1 if column_3   unless 1 == n     errors.add(:base, "Exactly one column from " +       "column_1, column_2, column_3 must be present")   end end 

To be clear, I'm looking for PSQL, not Ruby, here. I just wanted to show the logic I'm using since it is more compact than enumerating all "truth table" possibilities.

Answer 1

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