Postgres constraint ensuring one column of many is present?

Question

What are good ways to add a constraint to PostgreSQL to check that exactly one column (from a set of columns) contains a non-null value?

Update: It is likely that I want to use a check expression as detailed in Create Table and Alter Table.

Update: I'm looking through the available functions.

Update: Just for background, here is the Rails validation logic I'm currently using:

validate :multi_column_validation
def multi_column_validation
  n = 0
  n += 1 if column_1
  n += 1 if column_2
  n += 1 if column_3
  unless 1 == n
    errors.add(:base, "Exactly one column from " +
      "column_1, column_2, column_3 must be present")
  end
end

To be clear, I'm looking for PSQL, not Ruby, here. I just wanted to show the logic I'm using since it is more compact than enumerating all "truth table" possibilities.

Answer 1

Here is an elegant two column solution according to the "constraint -- one or the other column not null" PostgreSQL message board:

ALTER TABLE my_table ADD CONSTRAINT my_constraint CHECK (
  (column_1 IS NULL) != (column_2 IS NULL));

(But the above approach is not generalizable to three or more columns.)

If you have three or more columns, you can use the truth table approach illustrated by a_horse_with_no_name. However, I consider the following to be easier to maintain because you don't have to type out the logical combinations:

ALTER TABLE my_table
ADD CONSTRAINT my_constraint CHECK (
  (CASE WHEN column_1 IS NULL THEN 0 ELSE 1 END) +
  (CASE WHEN column_2 IS NULL THEN 0 ELSE 1 END) +
  (CASE WHEN column_3 IS NULL THEN 0 ELSE 1 END) = 1;

To compact this, it would be useful to create a custom function so that the CASE WHEN column_k IS NULL THEN 0 ELSE 1 END boilerplate could be removed, leaving something like:

(non_null_count(column_1) +
non_null_count(column_2) +
non_null_count(column_3)) = 1

That may be as compact as PSQL will allow (?). That said, I'd prefer to get to this kind of syntax if possible:

non_null_count(column_1, column_2, column_3) = 1