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" 🙂
Ugh! Anyone who has such a requirement has a broken schema…
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;
$$;
@David Fetter:
this function of course simplifies writing in case we are dealing with something else than numbers.
For just 2 fields you could simplify:
check ((a + b) IS NULL) — numbers
or
check ((a || b) IS NULL) — text