Question 301647 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Merging similar tables in PostGres

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
=> create table Pris (rank integer primary key, value bigint unique);
CREATE TABLE

In other words both rank and value must be unique.

The two tables hold data:

=> select * from Prix;
 rank | value 
------+-------
    1 |  1229
    2 |  1993
(2 rows)

=> select * from Pris;
 rank | value 
------+-------
    1 |  2719
    2 |  3547
(2 rows)

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
(4 rows)

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    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)_; 
    enter code here

refresh materialized view prix_pris_ranks;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search