Merging similar tables in PostGres

Question

Here is a question concerning a basic DB query in PostGres.

I have two tables created as follow:

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

In other words both rank and value must be unique.

The two tables hold data:

select * from Prix;

rank	value
1	1229
2	1993

select * from Pris;

rank	value
1	2719
2	3547

I want to know the proper query that I should apply so that the table Pris will be unchanged and the table Prix will become:

select * from Prix;

rank	value
1	1229
2	1993
3	2719
4	3547

The table Prix is now the result of a merging, with an adjustment of the rank field.

I tried playing with these queries (but failed):

INSERT INTO Prix SELECT * FROM Pris ON CONFLICT DO NOTHING;

INSERT INTO Prix SELECT * FROM Pris ON CONFLICT(rank) SET rank=rank+10;

I can see why the first query does not work.

But the second one does not work either. So I must be using an incorrect syntax or doing something wrong.

Any hint or relevant information will be appreciated.

Answer 1

This would not insert duplicate Values.

and you must be carefull , when multiple instances would run the query, that the number would produce anerror

INSERT INTO Prix 
  SELECT 
  ROW_NUMBER() OVER(ORDER BY "rank") + ( SELECT MAX("rank") FROM Prix),
  "value"
  FROM Pris ON CONFLICT ("value") DO NOTHING;

INSERT 0 2

SELECT * FROM Prix

rank	value
1	1229
2	1993
3	2719
4	3547

SELECT 4

fiddle

if you haven't duplicates in value it suffice, to

INSERT INTO Prix 
  SELECT 
  ROW_NUMBER() OVER(ORDER BY "rank") + ( SELECT MAX("rank") FROM Prix),
  "value"
  FROM Pris ON CONFLICT  DO NOTHING;

INSERT 0 2

SELECT * FROM Prix

rank	value
1	1229
2	1993
3	2719
4	3547

SELECT 4

fiddle