Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Prix (rank integer primary key, value bigint unique);
create table Pris (rank integer primary key, value bigint unique);
insert into prix values 
    (1,1229),
    (2,1993),
    (3,2800)
returning *;
insert into pris values
    (1,2719),
    (2,3547)
returning *;

CREATE TABLE

CREATE TABLE

rank
value

1
1229

2
1993

3
2800

INSERT 0 3

rank
value

1
2719

2
3547

INSERT 0 2

--view
--note the rank is adjusted for prix row (3,2800) to (4,2800) and others affected by it
create view v_prix_pris_ranks
as select row_number()over(order by value asc) as rank, 
          value
from (select value from pris
      union
      select value from prix)_; 

select * from v_prix_pris_ranks;

CREATE VIEW

rank
value

1
1229

2
1993

3
2719

4
2800

5
3547

SELECT 5

--matview
--note the rank is adjusted for prix row (3,2800) to (4,2800) and others affected by it
create materialized view mv_prix_pris_ranks
as select row_number()over(order by value asc) as rank, 
          value
from (select value from pris
      union
      select value from prix)_; 

select * from mv_prix_pris_ranks;

SELECT 5

rank
value

1
1229

2
1993

3
2719

4
2800

5
3547

SELECT 5

--reinsert
--note the rank is adjusted for prix row (3,2800) to (4,2800) and others affected by it
begin;

with old_prix as (delete from prix returning value)
insert into prix 
select row_number()over(order by value asc) as rank, 
       value
from (select value from old_prix
      union
      select value from pris)_;

select * from prix;

rollback;

BEGIN

INSERT 0 5

rank
value

1
1229

2
1993

3
2719

4
2800

5
3547

SELECT 5

ROLLBACK

--identity columns
--note the rank is NOT adjusted for prix row (3,2800)
begin;

alter table prix 
   alter column rank 
   add generated by default as identity 
   (restart 4);
alter table pris  
   alter column rank 
   add generated by default as identity 
   (restart 3);

select * from prix;

BEGIN

ALTER TABLE

ALTER TABLE

rank
value

1
1229

2
1993

3
2800

SELECT 3

select * from pris;

rank
value

1
2719

2
3547

SELECT 2

--note the rank is NOT adjusted for prix row (3,2800)
INSERT INTO Prix(value) SELECT value FROM Pris ON CONFLICT(value) DO NOTHING;
select * from prix;

rollback;

INSERT 0 2

rank
value

1
1229

2
1993

3
2800

4
2719

5
3547

SELECT 5

ROLLBACK

--supplementing without reinsertion
begin;

insert into prix select m+row_number()over(), value 
from pris,(select max(rank) from prix)_(m)
on conflict(value) do nothing;

--Without first shifting all ranks out of their target range,
--this would only work only in theory because 
--there's no way to force PostgreSQL to run an update in any specific order
with m as materialized(select max(rank) from prix)
update prix set rank=rank+max from m;

--Without the ordered update or shifting outside the target range
--individual rows violate uniqueness during the update operation, 
--when they try to swap places according to the new order
with ranks as (
select row_number()over(order by value asc) as rank, 
       value
from prix)
update prix p 
set rank=r.rank
from ranks r 
where r.value=p.value;

select * from prix;

rollback;

BEGIN

INSERT 0 2

UPDATE 5

UPDATE 5

rank
value

1
1229

2
1993

3
2719

4
2800

5
3547

SELECT 5

ROLLBACK