Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.

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)

or

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

One thought on “Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.”

  1. Way back when, I used to do this in CHECK constraints based on the fact that a boolean true casts to an integer 1.

    create table test (
    id serial primary key,
    a_id int4,
    b_id int4,
    c_id int4,
    d_id int4,
    e_id int4,
    CHECK(
    (a_id IS NULL)::integer +
    (b_id IS NULL)::integer +
    (c_id IS NULL)::integer +
    (d_id IS NULL)::integer +
    (e_id IS NULL)::integer = 1
    )
    );

    It worked, but it was clunky and non-obvious upon inspection what was going on.

Comments are closed.