Constraint - one boolean row is true, all other rows false

Variant 1

All you need is a single row with standard = true, so set standard to null in all other rows. Then a plain UNIQUE constraint works, since null values do not violate it. See:

• Create unique constraint with null columns

CREATE TABLE taxrate ( taxrate int PRIMARY KEY , standard bool DEFAULT true , CONSTRAINT standard_true_or_null CHECK (standard) -- that's all , CONSTRAINT standard_only_1_true UNIQUE (standard) ); 

DEFAULT true is an optional reminder to set the first row entered to true. It's not enforcing anything. While you cannot have more than one row with standard = true this way, you can still set all rows to null. There is no clean way to prevent this with only constraints in a single table. CHECK constraints do not consider other rows (without dirty tricks).

Related:

• Restrict two specific column values from existing at the same time
• Add constraint to make column unique per group of rows

To update:

BEGIN; UPDATE taxrate SET standard = NULL WHERE standard; UPDATE taxrate SET standard = TRUE WHERE taxrate = 2; COMMIT; 

While you set to null first, the UNIQUE constraint is never violated. To allow any order of updates, where the constraint is only satisfied at the end of the transaction, the UNIQUE constraint would have to be DEFERRABLE.
Or use a data-modifying CTE, works even with (default) NOT DEFERRABLE constraint:

WITH kingdead AS ( UPDATE taxrate SET standard = true WHERE taxrate = 1 ) UPDATE taxrate SET standard = null WHERE standard; -- works because effect of UPDATE not visible, yet 

See:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Or (while you don't need a deferrable constraint or FK constraint pointing to this row) you can also use a partial UNIQUE index instead. Less formal, equally effective:

ALTER TABLE taxrate DROP CONSTRAINT IF EXISTS standard_only_1_true; CREATE UNIQUE INDEX taxrate_standard_only_1_true ON taxrate (standard) WHERE standard; -- that's all, only true qualifies 

Reduces the index to a single row.

fiddle

Variant 2

Have a second table with a single row like:

Create this as superuser:

CREATE TABLE taxrate ( taxrate int PRIMARY KEY ); CREATE TABLE taxrate_standard ( taxrate int PRIMARY KEY REFERENCES taxrate ); -- singleton! CREATE UNIQUE INDEX taxrate_standard_singleton ON taxrate_standard ((true)); REVOKE DELETE ON TABLE taxrate_standard FROM public; -- can't delete INSERT INTO taxrate (taxrate) VALUES (42); INSERT INTO taxrate_standard (taxrate) VALUES (42); 

Now there is always a single row pointing to the standard (in this simple case also representing the standard rate directly). Only a superuser could break it. You might disallow that, too, with a trigger BEFORE DELETE.

dbfiddle here

Related:

• Constraint to enforce "at least one" or "exactly one" in a database

You might add a VIEW to see the same as in variant 1:

CREATE VIEW taxrate_combined AS SELECT t.*, (ts.taxrate = t.taxrate) AS standard FROM taxrate t LEFT JOIN taxrate_standard ts USING (taxrate); 

In queries where all you want is the standard rate, use (only) taxrate_standard.taxrate directly.

---

You later added:

There is a FK between products.tax_rate_id and tax_rate.id

A poor man's implementation of variant 2 would be to just add a row to products (or any similar table) pointing to the standard tax rate; a dummy product you might call "Standard tax rate" - if your setup allows it.

The FK constraints enforces referential integrity. To complete it, enforce tax_rate_id IS NOT NULL for the row (if that's not the case for the column in general). And disallow its deletion. Both could be put into triggers. No extra table, but less elegant and not as reliable.

You can use a Filtered index

create table test ( id int primary key, foo bool ); 

CREATE UNIQUE INDEX only_one_row_with_column_true_uix ON test (foo) WHERE (foo); --> where foo is true 

insert into test values (1, false); insert into test values (2, true); insert into test values (3, false); insert into test values (4, false); insert into test values (5, true); 

 ERROR: duplicate key value violates unique constraint "only_one_row_with_column_true_uix" DETAIL: Key (foo)=(t) already exists. 

dbfiddle here

---

But as you said, first row must be true, then you could use a CHECK constraint, but even using a function you can delete first row later.

create function check_one_true(new_foo bool) returns int as $$ begin return ( select count(*) + (case new_foo when true then 1 else 0 end) from test where foo = true ); end $$ language plpgsql stable; 

alter table test add constraint ck_one_true check(check_one_true(foo) = 1); 

insert into test values (1, true); insert into test values (2, false); insert into test values (3, false); insert into test values (4, false); 

insert into test values (5, true); 

 ERROR: new row for relation "test" violates check constraint "ck_one_true" DETAIL: Failing row contains (5, t). 

select * from test; 

 id | foo -: | :-- 1 | t 2 | f 3 | f 4 | f 

delete from test where id = 1; 

dbfiddle here

---

You could solve it by adding a BEFORE DELETE trigger to ensure first row (foo is true) is never deleted.

create function dont_delete_foo_true() returns trigger as $x$ begin if old.foo then raise exception 'Can''t delete row where foo is true.'; end if; return old; end; $x$ language plpgsql; 

create trigger trg_test_delete before delete on test for each row execute procedure dont_delete_foo_true(); 

delete from test where id = 1; 

ERROR: Can't delete row where foo is true.

dbfiddle here