In PostgreSQL, how do I make it so that either column A or column B must be non-null?

Question

I have a table with an "e-mail address" and "P2PMail address" column. The user is required to either enter an e-mail or P2PMail address.

If I set both to NOT NULL, then both must be filled in for a record to be created.

If I allow both to be NULL, then a user would be entirely unreachable.

If I set a UNIQUE CONSTRAINT on both columns, then the combination of them must be unique, but it says nothing about what I'm trying to do... unless I'm mis-thinking here.

What is the correct way to accomplish this? I fear that the answer is something unpleasant such as "triggers" or something. (I was never comfortable using those.)

Answer 1

You need a table-level check constraint:

alter table <name>
  add constraint either_email
    check (email is not null or p2pmail is not null);

If you're only allowed to enter one, but not both:

alter table <name>
  add constraint either_email
    check (email is null <> p2pmail is null);

The second form is possibly a little confusing at first glance: what it does is compare both columns' null status -- they aren't allowed to be the same.

The constraint can also be created simultaneously with the table:

create table <name> (
  ... columns ...
  constraint either_email check (email is not null or p2pmail is not null)
);

Answer 2

I like using num_nonnulls for this:

alter table the_table
  add constraint check_at_least_one_email
  check (num_nonnulls(email, p2pmail) > 0);

I prefer this because it can easily be extended to multiple columns.

If you also want to deal with empty strings:

alter table the_table
  add constraint check_at_least_one_email
  check (num_nonnulls(nullif(trim(email),''), nullif(trim(p2pmail),'')) > 0);

If you require exactly one non-null value change > 0 to = 1