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

create table vereine(
    team varchar(20) primary key,
    punkte int not null,
    serie int not null
)
;

CREATE TABLE


create or replace function update_points()
returns trigger as 
$BODY$
begin 
    if EXISTS (select 1 FROM vereine WHERE team = new.team ) then 
    update vereine
      set punkte = new.punkte 
    where team = new.team;
    RETURN NULL;
     else
    RETURN NEW;
    end if;
end;
$BODY$
LANGUAGE plpgsql;

CREATE FUNCTION


create trigger prevent_redundancy
before insert on vereine 
 for each row 
execute procedure update_points() ;

CREATE TRIGGER


insert into vereine(team,punkte,serie) values('JuventusFC', 50, 1);
insert into vereine(team,punkte,serie) values('AS Roma', 30, 1);
insert into vereine(team,punkte,serie) values('ParmaCalcio1913', 25, 1);
insert into vereine(team,punkte,serie) values('Palermo', 37, 2);
insert into vereine(team,punkte,serie) values('Pescara', 32, 2);
insert into vereine(team,punkte,serie) values('Spezia', 26, 2);
insert into vereine(team,punkte,serie) values('Carrarese Calcio', 34, 3);
insert into vereine(team,punkte,serie) values('Virtus Entella', 31, 3);
insert into vereine(team,punkte,serie) values('Juventus U-23', 50, 3);


INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from vereine;


team
punkte
serie


JuventusFC
50
1

AS Roma
30
1

ParmaCalcio1913
25
1

Palermo
37
2

Pescara
32
2

Spezia
26
2

Carrarese Calcio
34
3

Virtus Entella
31
3

Juventus U-23
50
3



SELECT 9


insert into vereine(team,punkte,serie) values('JuventusFC', 53, 1);

INSERT 0 0


select * from vereine;


team
punkte
serie


AS Roma
30
1

ParmaCalcio1913
25
1

Palermo
37
2

Pescara
32
2

Spezia
26
2

Carrarese Calcio
34
3

Virtus Entella
31
3

Juventus U-23
50
3

JuventusFC
53
1



SELECT 9

team	punkte	serie
JuventusFC	50	1
AS Roma	30	1
ParmaCalcio1913	25	1
Palermo	37	2
Pescara	32	2
Spezia	26	2
Carrarese Calcio	34	3
Virtus Entella	31	3
Juventus U-23	50	3