Synchronizing data between two tables in a certain way-CodePudding

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 )

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
if the NAME of new row in table1 doesnt exist in view than create new row with same qty as in Table1 new row

CodePudding user response：

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 ...