September 19th, 2007 by depesz | Tags: , | 4 comments »
Did it help? If yes - maybe you can help me?

cortilap @ freenode's #postgresql asked about how to create a check() that will allow only one of the columns to be not null.

it doesn't sound cool, let's see:

with 2 columns (a,b) you make a check: check ( (a is not null and b is null) or (a is null and b is not null) or (a is null and b is null))

whoa. and what about 3 columns? 4?

of course it creates some questions about the schema, but is there a way to do it? without such long checks?

one solution is to make a function to check it. but perhaps a simpler solution is possible?

luckily all of the fields are ints.

a quick think, and here we go:

check (coalesce(a*0, 1) + coalesce(b*0, 1) + coalesce(c*0, 1) > 1)

and what is the field was text? same thing, but instead of doing “X"*0, i would do “length(X)*0" 🙂

  1. 4 comments

  2. # Rodrigo De León
    Sep 20, 2007

    Ugh! Anyone who has such a requirement has a broken schema…

  3. Sep 20, 2007

    Your solution only works for number-ish things. Better to use a function that wraps CASE instead.

    CREATE OR REPLACE FUNCTION null_zero(ANYELEMENT)
    RETURNS INT
    LANGUAGE SQL
    AS $$
    SELECT
    CASE WHEN $1 IS NULL THEN 0 ELSE 1 END;
    $$;

  4. Sep 21, 2007

    @David Fetter:
    this function of course simplifies writing in case we are dealing with something else than numbers.

  5. # Erwin Brandstetter
    Sep 23, 2007

    For just 2 fields you could simplify:
    check ((a + b) IS NULL) — numbers
    or
    check ((a || b) IS NULL) — text

Sorry, comments for this post are disabled.