Search code examples
sqlpostgresqlself-updating

Synchronizing data between two tables in a certain way


PostgreSQL. I have two tables. I add data to the first table time by time using insert for all columns.

Table1:

CREATE TABLE purchases (
id INTEGER,
name VARCHAR,
qty INTEGER,
date TIMESTAMP,
price NUMERIC,
about VARCHAR )

Table2:

CREATE TABLE result (
id INTEGER,
name VARCHAR,
qty INTEGER,
date TIMESTAMP,
profit NUMERIC,
sold NUMERIC )
  1. If the NAME (field) of new row in table1 exists in view than just sum the qty of this new row to row qty with such name in view

  2. if the NAME of new row in table1 doesnt exist in view than create new row with same qty as in Table1 new row


Solution

  • A VIEW will 'automatically' reflect any changes done in Table1:

    CREATE VIEW Table2 AS
    SELECT 
       name, 
       SUM(qty) as Qty,
       MIN(date) as Date,
       AVG(price) as Price,
       '' as about)
    FROM Table1
    GROUP BY name
    

    After this, you can view the result using:

    SELECT * FROM Table2
    

    NOTE: of course, when creating a view you should try to avoid naming it something like Table2 ... 😉