Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

create table Prix (rank integer primary key, value bigint unique);

CREATE TABLE

create table Pris (rank integer primary key, value bigint unique);

CREATE TABLE

insert into Prix (rank, value)values(1,2347);
insert into Prix (rank, value)values(2,3547);

INSERT 0 1

INSERT 0 1

insert into Pris (rank, value)values(1,2587);
insert into Pris (rank, value)values(2,4547);

INSERT 0 1

INSERT 0 1

WITH merged_data AS (
  SELECT
    rank + GREATEST(MAX(rank) OVER (), 0) + 1 AS new_rank,
    value
  FROM Pris
)
INSERT INTO Prix (rank, value)
SELECT new_rank, value
FROM merged_data;

INSERT 0 2

SELECT * FROM Prix;

rank
value

1
2347

2
3547

4
2587

5
4547

SELECT 4