Can I add a constraint to multiple boolean columns so that at least one must be true?-CodePudding

I have a table in a postgresql 9.3 database that lists clients and the payment types they accept as booleans. A client can accept multiple payment types but they have to accept at least one payment type. So as an example:

Table format:

 --------- -------------- -------------- -------------- 
| clients | paymentType1 | paymentType2 | paymentType3 | 
 --------- -------------- -------------- -------------- 
| client1 | true         | true         | false        |
| client2 | true         | false        | true         |
| client3 | false        | false        | false        |
 --------- -------------- ----------------- -----------

Clients 1 and 2 are valid, client 3 is not valid as they have no payment types. Is there a way to write a constraint to enforce this - so that for any row, at least one of those paymentType booleans must be true?

CodePudding user response：

You may add the following check constraint:

ALTER TABLE yourTable   
ADD CONSTRAINT payment_cnstr CHECK (
    GREATEST(paymentType1, paymentType2, paymentType3) = true
);

The GREATEST() function above will only return true if at least one of the 3 columns have a true value.

CodePudding user response：

Simply check if any of them are true in a check constraint:

alter table tablename   
add constraint const_name check (paymentType1 or paymentType2 or paymentType3);

CodePudding user response：

The PostgreSQL documentation has full information on multi-column constraints.

The constraint should be:

CREATE TABLE clients (
    client0 bool,
    client1 bool,
    client2 bool,
    CHECK((client0 OR client1 OR client2) = true)
);

insert into clients(client0,client1,client2)
values (true, true, false);

insert into clients(client0,client1,client2)
values (false, false, false);

The second insert will fail.

db-fiddle