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

Answer 2

If rank is really just a primary key and you want to make an adjustment of the rank field to only keep it from colliding, make it an identity column. That way, you don't have to populate, address and maintain it at all. Demos at db<>fiddle:

create table Prix (
    rank integer generated by default as identity primary key, 
    value bigint unique);
create table Pris (
    rank integer generated by default as identity primary key, 
    value bigint unique);

You can also turn existing columns into identity columns. With minor adjustment, this makes your on conflict attempt work:

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 4);

INSERT INTO Prix(value) SELECT value FROM Pris ON CONFLICT(value) DO NOTHING;

---

In case you have an allergy to identity columns or just don't feel like altering your tables, only looking to shift the ranks to prevent conflicts, grab the current top rank and shift others out of the way based on that:

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

Or

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

Or stuff it in a scalar subquery right in the select list. Still assuming rank is only a PK, there's no need to order anything, inside the window clause of row_number()over() - skipping that saves some work, increasing performance while achieving the same effect as row_number() will generate a sequence of unique ranks starting from the offset introduced by max() in either case.

The planner can detect the subquery is uncorrelated, and therefore can be evaluated once, its result cached and re-used. PostgreSQL doesn't suffer from dirty read so throughout the statement, that value would be frozen in an MVCC snapshot - there's no scenario where re-checking it for every row could make sense.

---

If rank is supposed to reflect the value's rank in lowest-to-highest order, you can perform a reinsert:

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)_;

Which just reads values from both tables, pools them together and deduplicates using union, then gives them a fresh rank according to their current ascending order.

Adjusting ranks without reinserting is shown at the end of the demo, but it requires two updates to work around PostgreSQL uncontrollable order of update.

You might be better off with a third relation, a view that does this dynamically:

create view 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)_; 

Or a materialized view that does this once and again whenever you tell it to refresh:

create materialized view 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)_; 

refresh materialized view prix_pris_ranks;

Answer 3

To be able to get your desired output and to merge the data from "Pris" into "Prix" while adjusting the "rank" field in "Prix" we can use a simple common table expression query to solve the the question above

The query can be as follows:

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;

The following query with a CTE called "merged_data" will compute a new rank for each row in the "Pris" table. This new rank is generated by adding the existing rank, the maximum rank found in the entire "Pris" table, and 1. The final step involves inserting the calculated new ranks along with the existing "value" column into the "Prix" table, effectively updating the "rank" column in the destination table.

Further illustration and implementation of the following question : https://dbfiddle.uk/kLcxbYNw

Hope this helps.