Postgres 10

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

select version();

version

PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

CREATE TABLE IF NOT EXISTS Polls (
  id SERIAL PRIMARY KEY,
  options text[]
);

CREATE TABLE IF NOT EXISTS Votes (
  id SERIAL PRIMARY KEY,
  poll_id integer references Polls(id),
  value integer NOT NULL ,
  cast_by integer NOT NULL
);

CREATE TABLE

CREATE TABLE

create or replace function id_exist() returns trigger as
$$
begin
if new.value<0 or new.value>=(select array_length(options,1) from polls where id=new.poll_id) then
raise exception 'value is not in range';
end if;
return new;
end;

$$
language plpgsql

CREATE FUNCTION

CREATE TRIGGER check_value BEFORE INSERT  ON votes
    FOR EACH ROW EXECUTE PROCEDURE id_exist();

CREATE TRIGGER

insert into polls values(1,'{A,B,C,D}');
insert into polls values(2,'{A,B,C}');
insert into votes(poll_id,value,cast_by) values(1,2,1);

INSERT 0 1

INSERT 0 1

INSERT 0 1

insert into votes(poll_id,value,cast_by) values(1,3,1);

INSERT 0 1

select * from votes

id
poll_id
value
cast_by

1
1
2
1

2
1
3
1

SELECT 2