NOT NULL constraint over a set of columns
constraints, database, postgresql, postgresql-9.2, sql
Solution
@Igor is quite right, processing a couple of `OR`'ed expressions is fast and simple.
For a long list of columns (`a`, `b`, `c`, `d`, `e`, `f`, `g` in the example), this is shorter and just as fast:
CHECK (NOT (a,b,c,d,e,f,g) IS NULL)
db<>fiddle here Old sqlfiddle
How does it work?
A more verbose form of the above would be:
CHECK (NOT ROW(a,b,c,d,e,f,g) IS NULL)
`ROW` is redundant syntax here.
Testing a `ROW` expression with `IS NULL` only reports `TRUE` if every single column is `NULL` - which happens to be exactly what we want to exclude.
It's not possible to simply reverse this expression with `(a,b,c,d,e,f,g) IS NOT NULL`, because that would test whether every single column `IS NOT NULL`. Instead, negate the whole expression with `NOT`. Voilá.
More details in the manual here and here.
An expression of the form:
CHECK (COALESCE(a,b,c,d,e,f,g) IS NOT NULL)
would achieve the same, less elegantly and with a major restriction: only works for columns of matching data type, while the check on a `ROW` expression works with any mix of data types.
Problem
I have a table in Postgres which currently has a `NOT NULL` constraint on it's `email` column. This table also has a `phone` column which is optional. I would like the system to accept some records without `email` but only if these have `phone` as `NOT NULL`. In other words, I need a `NOT NULL` database constraint such that `CREATE` or `UPDATE` queries succeed without any errors if either or both of `email` or `phone` fields are present. Extending the above further, is it possible in Postgres, to specify a set of column names, one or more of which should be `NOT NULL` for the record to be successfully updated or created?