PostgreSQL Trigger to Update Inserted Row With Data From Another Table I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels &amp; meta-data).
I am trying to create a PostgreSQL trigger to update the sensor data as they are inserted into SENSORS. Each row of sensor data will be updated with the corresponding label name from LABELS. Unable to get the row update to work. Sample shown below:
Sample Dataset
CREATE TABLE sensor (
   datavalue integer, 
   created timestamp,
   label_id integer,
   label_name text);

CREATE TABLE labels (
   id integer,
   label_id integer,
   label_name text);

Insert into labels values (1, 215, 'Home'), (2, 216, 'Shop'), (3, 217, 'Flat'), (4, 218, 'Street');
Insert into sensor values (67, '2020-09-02 08:40:07.354', 215),(67, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

Trigger Function
CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS 
$func$
BEGIN
UPDATE sensor 
    SET label_name = b.label_name 
    from labels b
    where new.label_id = b.label_id;
     RETURN NEW; 
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
AFTER INSERT OR UPDATE ON sensor
FOR EACH ROW EXECUTE PROCEDURE update_name();

Test row insertion
Insert into sensor values (78, '2020-09-02 08:40:07.354', 215),(77, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

I have a very large number of rows flowing in, and would like to update each row as it is inserted.
Any assistance greatly appreciated. Thanks!
Most likely you're going in wrong direction but I have only gut feelings for that.
But, you can play with triggers - maybe it will do a work. However it seems that you will have massive updates in each trigger call because you are updating all rows from sensor with new label, which seems just wrong.
As I'm not sure about your specific needs, I will just push you in most proper way (I hope) with these changes:

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO sensor(datavalue, created, label_id, label_name)
        SELECT NEW.datavalue, NEW.created, NEW.label_id, b.label_name
        from labels b
        where new.label_id = b.label_id;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE sensor
        SET label_name = b.label_name
        from labels b
        where new.label_id = b.label_id;

    END IF;
    RETURN NULL;

END
$$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
BEFORE INSERT OR UPDATE ON sensor
FOR EACH ROW
WHEN (pg_trigger_depth() &lt; 1)
EXECUTE PROCEDURE update_name();

Here, you do these changes instead (before) insert or update. Insert will be proper, update needs to be adjusted or removed as from your description it seems like you will be making only inserts here.
And please note that there is limiter as proposed here https://dba.stackexchange.com/a/103661/213360.