PSQL Constraint based on column value

Is it possible to have a Constraint but only when one column is set to a particular value. For example take this pseudo-code example of a President which checks to make sure there is never more than 1 President at any time (note, this is NOT valid psql syntax)

CREATE TABLE president (
    id BIGSERIAL PRIMARY KEY,
    current BOOLEAN NOT NULL,
    CONSTRAINT there_can_be_only_one CHECK(COUNT(current=true)<=1)
);
    

Answer

You can use the so called partial index to enforce this specific constraint. In SQL Server they are called filtered indexes.

CREATE UNIQUE INDEX IX ON president (current)
WHERE current = true;

This index should prevent having more than one row in a table with current value set to true, because it is defined as unique.