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 test
(
    id int primary key,
    foo bool
);

CREATE TABLE


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 FUNCTION


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

CREATE TRIGGER


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;

CREATE FUNCTION


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

ALTER TABLE


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 0 1


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



SELECT 4


delete from test where id = 1;

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