How can I ensure that Column_A can only have a value if Column_B is NULL? And vice versa

Question

I'm attempting to create a table that has three columns:

id

paid_at

failed_at

How can I make sure that paid_at can only have a value if failed_at is NULL?

Here is my current code:

 CREATE TABLE charges(
      id        TEXT     NOT NULL     PRIMARY KEY,
      paid_at   TEXT,
      failed_at TEXT
    );

    ALTER TABLE charges
      ADD CONSTRAINT paid_at CHECK (failed_at IS NULL);

    ALTER TABLE charges
      ADD CONSTRAINT failed_at CHECK (paid_at IS NULL);

I also want to make sure that BOTH cannot be null.

How can I do this?

Thanks!

Answer 1

You can use num_nonnulls() in the check constraint:

alter table charges 
  add constraint only_one_not_null 
  check (num_nonnulls(paid_at, failed_at) = 1);

That ensure that exactly one of the columns is not null and the other is null.

If you consider a string with only spaces to be "null" as well, you could extend that to:

alter table charges 
  add constraint only_one_not_null 
  check (num_nonnulls(nullif(trim(paid_at),''), nullif(trim(failed_at),'')) = 1);

Answer 2

I am inclined to do this with addition. To check that one of a group of columns is not null, count the number of not-null values:

check ( (paid_at is not null)::int + (failed_at is not null)::int) > 0 )

Answer 3

You can use the following predicate:

alter table charges add constraint exclusive_rule check (
  paid_at is null and failed_at is not null or
  paid_at is not null and failed_at is null
);