Postgres constraint ensuring one column of many is present?

by

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. 🙂

Leave a Comment

tech