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 16.6 (Debian 16.6-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
create table questions( id integer generated always as identity
primary key
, question text not null unique
);
insert into questions(question)
values ('Q 1')
, ('Q 2');
select *
from questions;
CREATE TABLE
INSERT 0 2
| id | question |
|---|---|
| 1 | Q 1 |
| 2 | Q 2 |
SELECT 2
create table question_options( opt_id integer generated always as identity
primary key
, quest_id integer not null
references questions(id)
, tag text not null
, label text not null
, correct boolean not null default false
);
insert into question_options(quest_id, tag, label)
( values (1,'A', 'Option 1a')
, (1,'B', 'Option 1b')
, (1,'C', 'All the above')
, (1,'D', 'None of the above')
, (2,'A', 'Option 2aa')
, (2,'B', 'Option 2b')
, (2,'C', 'Option 2c')
, (2,'D', 'Option 2d')
);
select *
from question_options;
CREATE TABLE
INSERT 0 8
| opt_id | quest_id | tag | label | correct |
|---|---|---|---|---|
| 1 | 1 | A | Option 1a | f |
| 2 | 1 | B | Option 1b | f |
| 3 | 1 | C | All the above | f |
| 4 | 1 | D | None of the above | f |
| 5 | 2 | A | Option 2aa | f |
| 6 | 2 | B | Option 2b | f |
| 7 | 2 | C | Option 2c | f |
| 8 | 2 | D | Option 2d | f |
SELECT 8
create unique index "Can have only one Correct Answer"
on question_options(quest_id, correct) where correct;
CREATE INDEX
-- set correct options
with ans(qid, copt) as
( values (1,'D')
, (2,'B')
)
update question_options
set correct = true
where (quest_id, tag) in (select qid, copt
from ans
);
select *
from question_options
order by quest_id, tag;
UPDATE 2
| opt_id | quest_id | tag | label | correct |
|---|---|---|---|---|
| 1 | 1 | A | Option 1a | f |
| 2 | 1 | B | Option 1b | f |
| 3 | 1 | C | All the above | f |
| 4 | 1 | D | None of the above | t |
| 5 | 2 | A | Option 2aa | f |
| 6 | 2 | B | Option 2b | t |
| 7 | 2 | C | Option 2c | f |
| 8 | 2 | D | Option 2d | f |
SELECT 8
-- Attempt to set a second Correct value for 'Q 2'
update question_options
set correct = true
where quest_id = 2
and tag = 'D';
ERROR: duplicate key value violates unique constraint "Can have only one Correct Answer" DETAIL: Key (quest_id, correct)=(2, t) already exists.
-- But Correct was actually incorrectly specified for Q 2. Should be 'D'
update question_options
set correct = false
where quest_id = 2;
update question_options
set correct = true
where quest_id = 2
and tag = 'D';
select *
from question_options
order by quest_id, tag;
UPDATE 4
UPDATE 1
| opt_id | quest_id | tag | label | correct |
|---|---|---|---|---|
| 1 | 1 | A | Option 1a | f |
| 2 | 1 | B | Option 1b | f |
| 3 | 1 | C | All the above | f |
| 4 | 1 | D | None of the above | t |
| 5 | 2 | A | Option 2aa | f |
| 6 | 2 | B | Option 2b | f |
| 7 | 2 | C | Option 2c | f |
| 8 | 2 | D | Option 2d | t |
SELECT 8