Postgres 11

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 dbase234936 ( 
id integer,
serial varchar(7),
version timestamp,
is_deleted integer
);

insert into dbase234936 values ( 10 , 'AAAAAA' , '2019-04-14 15:28:08', 0);
insert into dbase234936 values ( 22 , 'AAAAAA' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 13 , 'AAAAAA' , '2019-02-10 15:28:08', 0);
insert into dbase234936 values ( 40 , 'BBBBBB' , '2019-04-17 15:28:08', 0);
insert into dbase234936 values ( 27 , 'BBBBBB' , '2019-02-20 15:28:08', 0);
insert into dbase234936 values ( 17 , 'CCCCCC' , '2019-03-04 15:28:08', 0);
insert into dbase234936 values ( 35 , 'CCCCCC' , '2019-01-01 15:28:08', 0);



1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from dbase234936;


id
serial
version
is_deleted


10
AAAAAA
2019-04-14 15:28:08
0

22
AAAAAA
2019-03-04 15:28:08
0

13
AAAAAA
2019-02-10 15:28:08
0

40
BBBBBB
2019-04-17 15:28:08
0

27
BBBBBB
2019-02-20 15:28:08
0

17
CCCCCC
2019-03-04 15:28:08
0

35
CCCCCC
2019-01-01 15:28:08
0


with windows as ( 
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
select id, serial, version, case when rn=1 then 0 else 1 end as is_deleted 
from windows
;


id
serial
version
is_deleted


10
AAAAAA
2019-04-14 15:28:08
0

22
AAAAAA
2019-03-04 15:28:08
1

13
AAAAAA
2019-02-10 15:28:08
1

40
BBBBBB
2019-04-17 15:28:08
0

27
BBBBBB
2019-02-20 15:28:08
1

17
CCCCCC
2019-03-04 15:28:08
0

35
CCCCCC
2019-01-01 15:28:08
1


with windows as ( 
  select id, serial, version, is_deleted, row_number() over (partition by serial order by version desc) as rn
  from dbase234936
)
update dbase234936 set is_deleted = case when rn=1 then 0 else 1 end
from windows
where dbase234936.id = windows.id;
7 rows affected

select * from dbase234936;


id
serial
version
is_deleted


10
AAAAAA
2019-04-14 15:28:08
0

13
AAAAAA
2019-02-10 15:28:08
1

17
CCCCCC
2019-03-04 15:28:08
0

22
AAAAAA
2019-03-04 15:28:08
1

27
BBBBBB
2019-02-20 15:28:08
1

35
CCCCCC
2019-01-01 15:28:08
1

40
BBBBBB
2019-04-17 15:28:08
0

id	serial	version
10	AAAAAA	2019-04-14 15:28:08
22	AAAAAA	2019-03-04 15:28:08
13	AAAAAA	2019-02-10 15:28:08
40	BBBBBB	2019-04-17 15:28:08
27	BBBBBB	2019-02-20 15:28:08
17	CCCCCC	2019-03-04 15:28:08
35	CCCCCC	2019-01-01 15:28:08