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?
@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)
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.