On 5th of February, Tom Lane committed patch:
Add num_nulls() and num_nonnulls() to count NULL arguments. An example use-case is "CHECK(num_nonnulls(a,b,c) = 1)" to assert that exactly one of a,b,c isn't NULL. The functions are variadic, so they can also be pressed into service to count the number of null or nonnull elements in an array. Marko Tiikkaja, reviewed by Pavel Stehule
This is actually pretty great. Every so often someone on irc asks, how to make sure that from given set of fields only one has value, and all other are null.
Solution to this can be simple, or tricky, depending on number of fields and your proficiency with plpgsql.
Now, this problem is gone, as we have these two new functions.
How they work? Let's see:
$ \df num_n* List OF functions Schema | Name | RESULT DATA TYPE | Argument DATA types | TYPE ------------+--------------+------------------+---------------------+-------- pg_catalog | num_nonnulls | INTEGER | VARIADIC "any" | normal pg_catalog | num_nulls | INTEGER | VARIADIC "any" | normal (2 ROWS)
In here, we see that they are both variadic which means they can take any number of arguments (well, to some limit, but that's not really important now).
So, I can:
$ SELECT num_nulls( NULL, 1, 2, 15, NULL, 3, NULL ); num_nulls ----------- 3 (1 ROW)
$ SELECT num_nonnulls( NULL, 1, 2, 15, NULL, 3, NULL ); num_nonnulls -------------- 4 (1 ROW)
And that's that simple.
How would you use these functions to solve the problem I mentioned earlier, from irc?
Let's assume we have a table with following schema:
$ CREATE TABLE test ( id serial PRIMARY KEY, a_id int4, b_id int4, c_id int4, d_id int4, e_id int4 ); CREATE TABLE
And now, let's assume that we want to make sure that in any given row, only one of a_id .. e_id are set, but it has to be set.
This can be trivially achieved now, with:
$ ALTER TABLE test ADD CHECK (num_nonnulls(a_id, b_id, c_id, d_id, e_id) = 1); ALTER TABLE
And with this in place, I can easily insert rows that have one of ids set:
$ INSERT INTO test (a_id) VALUES (1); INSERT 0 1 $ INSERT INTO test (b_id) VALUES (2); INSERT 0 1 $ INSERT INTO test (c_id) VALUES (3); INSERT 0 1 $ INSERT INTO test (d_id) VALUES (4); INSERT 0 1 $ INSERT INTO test (e_id) VALUES (5); INSERT 0 1
But I can't insert all-nulls, or more-than one not null:
$ INSERT INTO test (a_id) VALUES (NULL); ERROR: NEW ROW FOR relation "test" violates CHECK CONSTRAINT "test_check" DETAIL: Failing ROW contains (6, NULL, NULL, NULL, NULL, NULL). $ INSERT INTO test (a_id, b_id) VALUES (1,2); ERROR: NEW ROW FOR relation "test" violates CHECK CONSTRAINT "test_check" DETAIL: Failing ROW contains (7, 1, 2, NULL, NULL, NULL).
Nice. It was possible earlier, but new approach is much nicer. Thanks.