CODEWITHSUNDEEP
postgresqlconstraints
fadedbee
fadedbee

Reputation: 44807

Can a Postgres constraint limit a table to one true value, but many false values?

I have read: Could I make a column in a table only allows one 'true' value and all other rows should be 'false' - the same could be made to work in Postgres, but I'd like a one-table solution, if possible.

I have a logon_state table:

CREATE TABLE logon_state (
    username VARCHAR(20) references users,
    region region NOT NULL,
    channel channel NOT NULL,
    ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    callsign VARCHAR(10) NOT NULL,
    live BOOLEAN NOT NULL,
    PRIMARY KEY (username, region, channel)
);

How can I add a constraint or unique key such that for each region/channel combination only one row may have a live value of true while an unlimited number of rows can have a live value of false?

Upvotes: 2

Views: 2108

Answers (1)

user330315
user330315

Reputation:

That can be done with a partial unique index:

create unique index on logon_state (region, chanel)
  where live;

This assumes that the domains (or types) region and chanel can be compared properly. If those are record types with multiple fields, this will probably not work.

Upvotes: 5

Related Questions