Calculate average after insert in PostgreSQL

Question

I'm trying to get the average of the field pm10_ug_m3 of all the values introduced in the last 24 hours by a sensor, but with my current code the average does not include the value of the row inserted.

Currently as the trigger is done before the insert, it is not taking into account the last value inserted in the field pm10_ug_m3. For this reason the average introduced in the field is 3 and not 6.5 obtained from (10+3)/2

1) Creation of table and addition of some dates:

(
  ID bigint NOT NULL,
  SensorID character(10),
  pm10_ug_m3 numeric(10,2),
  tense timestamp without time zone,
  average float,
  CONSTRAINT id_pk PRIMARY KEY (ID)
);

INSERT INTO sensor (ID,SensorID,pm10_ug_m3,tense) VALUES
(1,'S16',1,'2019-07-10 04:25:59'),
(2,'S20',3,'2017-07-10 02:25:59');

2) Creation of the trigger to calculate the average of pm10_ug_m3 of the records captured in the last 24h from the same sensor:

CREATE OR REPLACE FUNCTION calculate_avg() RETURNS TRIGGER AS $BODY$
  BEGIN
  NEW.average := ( SELECT AVG(pm10_ug_m3)
                        FROM sensor
                        WHERE SensorID=NEW.SensorID 
                        AND tense>= NEW.tense - INTERVAL '24 HOURS');

  RETURN NEW;
  END;
  $BODY$
 LANGUAGE plpgsql;


CREATE TRIGGER calculate_avg_trigger BEFORE INSERT OR UPDATE 
    ON sensor FOR EACH ROW 
    EXECUTE PROCEDURE calculate_avg();

3) Insert of a new row, where it will be populated the field average:

INSERT INTO sensor (ID,SensorID,pm10_ug_m3,tense) VALUES
(3,'S20',10,'2017-07-10 04:25:59')

have you tried changing from BEFORE to AFTER on the CREATE TRIGGER statement? — IWHKYB

S-Man S-Man · Accepted Answer · 2019-07-10T12:30:36

This does not work because the AVG() function only considers the data which is still inserted, not the new data which will be inserted.

Changing the trigger point from BEFORE to AFTER would deliver a correct result indeed, but it will not be set because the INSERT already has been done at this point.

So one way to achieve your result is to calculate the average manually in your trigger function:

SELECT (SUM(pm10_ug_M3) + NEW.pm10_ug_m3) / (COUNT(pm10_ug_m3) + 1)
FROM ...

SUM() of the current values + the new divided by the COUNT() of the current values + the new one.

demo:db<>fiddle

Calculate average after insert in PostgreSQL

1 Answers