Postgres 9.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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)
);

CREATE TABLE


INSERT INTO taxrate (taxrate) VALUES (42);  -- becomes standard since not explicitly not the standard

INSERT 0 1


INSERT INTO taxrate (taxrate, standard) VALUES (1, NULL),  (2, NULL);

INSERT 0 2


INSERT INTO taxrate (taxrate) VALUES (43);  -- Fails.

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


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

COMMIT


--  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.