clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 400790 distinct fiddles created so far.

CREATE TABLE taxrate ( taxrate int PRIMARY KEY , standard bool DEFAULT true , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint , CONSTRAINT standard_only_1_true UNIQUE (standard) );
 hidden batch(es)


INSERT INTO taxrate (taxrate) VALUES (42); -- becomes standard since not explicitly not the standard
1 rows affected
 hidden batch(es)


INSERT INTO taxrate (taxrate, standard) VALUES (1, NULL), (2, NULL);
2 rows affected
 hidden batch(es)


INSERT INTO taxrate (taxrate) VALUES (43); -- Fails.
ERROR: duplicate key value violates unique constraint "standard_only_1_true" DETAIL: Key (standard)=(t) already exists.
 hidden batch(es)


BEGIN; UPDATE taxrate SET standard = NULL WHERE standard; UPDATE taxrate SET standard = TRUE WHERE taxrate = 2; COMMIT;
1 rows affected
1 rows affected
 hidden batch(es)


-- Fails: WITH kingdead AS ( UPDATE taxrate SET standard = NULL WHERE standard ) UPDATE taxrate SET standard = TRUE WHERE taxrate = 1; -- would be possibly with a more expensive DEFERRABLE INITIALLY IMMEDIATE constraint
ERROR: duplicate key value violates unique constraint "standard_only_1_true" DETAIL: Key (standard)=(t) already exists.
 hidden batch(es)