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

Postgresql – Calculate delta between two tables

I need to calculate delta between two tables. See the example:
First, I have tables A and B

table A

ID Value
Id 1 10
Id 2 20
Id 3 30
Id 4 40

table B

ID Value
Id 1 10
Id 2 15

So now in table B:

  1. there is no Id 3 and Id 4 in B.
  2. value of Id 2 differs.
  3. id 1 is the same in both tables

What I need to do is to calculate table C wich contains diffence between A and B

table C

ID Value
Id 2 -5
Id 3 30
Id 4 40

There is no Id A in table C because it has same values in both A and B.

Any help will be appreciated.
Thanks in advance.

PostgreSQL DB.

Any help will be appreciated.
Thanks in advance.

Tags:

2

Answers


  1. 0

    Assuming that both table always have the same columns, you can try the following approach –

    -- Insert rows from table A where ID doesn't exist in table B
INSERT INTO C (ID, Value)
SELECT A.ID, A.Value
FROM A
LEFT JOIN B ON A.ID = B.ID
WHERE B.ID IS NULL;

-- Insert rows from table B where ID exists in both tables A and B, but values are different
INSERT INTO C (ID, Value)
SELECT B.ID, B.Value - A.Value
FROM B
JOIN A ON A.ID = B.ID
WHERE B.Value <> A.Value;
    Login or Signup to reply.
  2. 0

    Heres the query, I have tested it here.

    INSERT INTO C (ID, Value)
SELECT A.ID, COALESCE(B.Value - A.Value, A.Value) AS Value
FROM A LEFT JOIN B ON A.ID = B.ID
WHERE A.Value <> COALESCE(B.Value, A.Value) OR B.ID IS NULL;

    COALESCE: Handle cases when there is no match in second table.

    Output:
    enter image description here

    I am expecting you have already created table C.
    I hope it helps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search