snippetsqlMajor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlmustcolumnnonnullmakeeitherthathow

Problem

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

Solution

You need a table-level check constraint:

alter table 
  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 
  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  (
  ... columns ...
  constraint either_email check (email is not null or p2pmail is not null)
);

Code Snippets

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

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

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

Context

StackExchange Database Administrators Q#284581, answer score: 27

Revisions (0)

No revisions yet.