Reduce the table fragmentation Postgresql

Question

I have a table with n elements with ids from 1 to n. Then I delete nearly all the data from the table, leaving only m elements, m << n, including element with id=n. Before deleting the difference between ids of adjacent rows was 1, now it could be much larger. I need to update the table such a way that remaining element ids were from 1 to m and difference between ids of adjacent rows was 1. How I can do this? I use PostgreSQL.

Answer 1

You may find a better option to not update the primary key (especially in heavy multiuser einvironment) but to delegate the removal of the gaps to the accessing view.

Simple Example

create table test as
select id from generate_series(1,5) id;

delete from test 
where id in (2,3,4);

The view assigns teh new continuous sequence using the row_number function.

create view test_gapless as
select 
id,
row_number() over (order by id) gapless_id
from test;

select * from test_gapless;

id, gapless_id
1   1
5   2