Postgres 16

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 team (
  team_id        int2 PRIMARY KEY
, remaining_time float NOT NULL  -- ? use date/time type for temporal data!
, description    text NOT NULL   -- ? description?
, difficulty     text NOT NULL
);

CREATE TABLE person (
  pers_id   int2 PRIMARY KEY
, full_name text NOT NULL
  -- others attribute
);

CREATE TABLE team_member (
  team_id int2 NOT NULL REFERENCES team
, pers_id int2 NOT NULL REFERENCES person
, level   int2 NOT NULL CHECK (level BETWEEN 1::int2 AND 5::int2)  -- can be from 1 to 5
, slot    "char" NOT NULL DEFAULT 'l'::"char"
, pos     "char" NOT NULL DEFAULT '1'::"char"
, CONSTRAINT team_member_pkey PRIMARY KEY (team_id, pers_id)
  -- team can have 1 leader and two members, period.
, CONSTRAINT chk_1lead_2member CHECK (slot = 'l' AND pos = '1'
                                   OR slot = 'm' AND pos IN ('2', '3'))
  -- 'l' ... leader, 'm' ... member
  -- leader must have pos '1', members must have pos '2' or '3'
, CONSTRAINT uni_team_slot_pos UNIQUE(slot, pos, team_id) INCLUDE (pers_id)
  -- team must have distinct persons; plus: provides essential index
);

-- add idx with reversed cols
CREATE UNIQUE INDEX team_member_pers_id_team_id ON team_member(pers_id, team_id);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE INDEX


INSERT INTO team VALUES
  (1, 1, '','')   
, (2, 1, '','')
, (3, 1, '','')
, (4, 1, '','')  -- encomplete
, (5, 1, '','')  -- empty
RETURNING *;

INSERT INTO person VALUES
  (1, 'Jon')   
, (2, 'Mai')
, (3, 'Dan')
, (4, 'Bill')
RETURNING *;

INSERT INTO team_member VALUES
  (1, 1, 1, 'l', '1')
, (1, 2, 1, 'm', '2')
, (1, 3, 1, 'm', '3')

, (2, 1, 1, 'l', '1')  -- "same" team
, (2, 3, 1, 'm', '2')
, (2, 2, 1, 'm', '3')

, (3, 2, 1, 'l', '1')  -- "different" team
, (3, 3, 1, 'm', '2')
, (3, 1, 1, 'm', '3')

, (4, 2, 1, 'l', '1')  -- "incomplete" team
, (4, 3, 1, 'm', '2')
RETURNING *;


team_id
remaining_time
description
difficulty


1
1



2
1



3
1



4
1



5
1





INSERT 0 5




pers_id
full_name


1
Jon

2
Mai

3
Dan

4
Bill



INSERT 0 4




team_id
pers_id
level
slot
pos


1
1
1
l
1

1
2
1
m
2

1
3
1
m
3

2
1
1
l
1

2
3
1
m
2

2
2
1
m
3

3
2
1
l
1

3
3
1
m
2

3
1
1
m
3

4
2
1
l
1

4
3
1
m
2



INSERT 0 11


-- distinct teams - excl. incomplete teams
SELECT DISTINCT ON (l.pers_id, m.m1, m.m2)
       team_id, l.pers_id AS lead, m.m1, m.m2
FROM   team_member l
JOIN  (
   SELECT team_id, min(pers_id) AS m1, max(pers_id) AS m2
   FROM   team_member m
   WHERE  m.slot = 'm'
   GROUP  BY 1
   HAVING count(*) = 2  -- exclude incomplete teams
   ) m USING (team_id)
WHERE  l.slot = 'l'
ORDER  BY l.pers_id, m.m1, m.m2, team_id;  -- pick team with smallest ID from dupes


team_id
lead
m1
m2


1
1
2
3

3
2
1
3



SELECT 2


-- select same teams as given team (1, 2, 3) - excl. incomplete teams
SELECT team_id, l.pers_id AS lead, m1.pers_id AS m1, m2.pers_id AS m2
FROM   team_member l
JOIN   team_member m1 USING (team_id)
JOIN   team_member m2 USING (team_id)
WHERE  l.slot = 'l'
AND    l.pers_id = 1  -- lead
AND    m1.slot = 'm'
AND    m1.pers_id = LEAST (2,3)  -- member 1
AND    m2.slot = 'm'
AND    m2.pers_id = GREATEST (2,3)   -- member 2
ORDER  BY lead, m1, m2;


team_id
lead
m1
m2


1
1
2
3

2
1
2
3



SELECT 2


-- Trying some illegal doperations team_member

-- dupe (team_id, pers_id)
INSERT INTO team_member VALUES
  (4, 3, 1, 'm', '2');

ERROR:  duplicate key value violates unique constraint "team_member_pkey"
DETAIL:  Key (team_id, pers_id)=(4, 3) already exists.


-- dupe team lead
INSERT INTO team_member VALUES
  (4, 4, 1, 'l', '1');

ERROR:  duplicate key value violates unique constraint "uni_team_slot_pos"
DETAIL:  Key (slot, pos, team_id)=(l, 1, 4) already exists.


-- illegal slot/pos combo 2
INSERT INTO team_member VALUES
  (4, 4, 1, 'm', '1');

ERROR:  new row for relation "team_member" violates check constraint "chk_1lead_2member"
DETAIL:  Failing row contains (4, 4, 1, m, 1).


-- illegal slot/pos combo 2
INSERT INTO team_member VALUES
  (5, 4, 1, 'l', '2');

ERROR:  new row for relation "team_member" violates check constraint "chk_1lead_2member"
DETAIL:  Failing row contains (5, 4, 1, l, 2).


-- pers_id does not exist
INSERT INTO team_member VALUES
  (5, 7, 1, 'l', '1');

ERROR:  insert or update on table "team_member" violates foreign key constraint "team_member_pers_id_fkey"
DETAIL:  Key (pers_id)=(7) is not present in table "person".


-- team_id does not exist
INSERT INTO team_member VALUES
  (7, 3, 1, 'l', '1');

ERROR:  insert or update on table "team_member" violates foreign key constraint "team_member_team_id_fkey"
DETAIL:  Key (team_id)=(7) is not present in table "team".


-- no additional member allowed
INSERT INTO team_member VALUES
  (1, 4, 1, 'm', '2');

ERROR:  duplicate key value violates unique constraint "uni_team_slot_pos"
DETAIL:  Key (slot, pos, team_id)=(m, 2, 1) already exists.