NOT NULL constraint over a set of columns

Question

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?

Answer 1

@Igor is quite right and a couple of OR'ed expression are 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 that 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 columns.

Answer 2

You can use CHECK constraint for this. Something like:

CHECK (email is not null OR phone is not null)

Details on constraints can be found here