Triggers

Timescale supports the full range of PostgreSQL triggers. Creating, altering, or dropping triggers on a hypertable propagates the changes to all of the underlying chunks.

Create a trigger

This example creates a new table called error_conditions with the same schema as conditions, but that only stores records which are considered errors. An error, in this case, is when an application sends a temperature or humidity reading with a value that is greater than or equal to 1000.

Creating a trigger

Create a function that inserts erroneous data into the error_conditions table:


CREATE OR REPLACE FUNCTION record_error()
  RETURNS trigger AS $record_error$
BEGIN
 IF NEW.temperature >= 1000 OR NEW.humidity >= 1000 THEN
   INSERT INTO error_conditions
     VALUES(NEW.time, NEW.location, NEW.temperature, NEW.humidity);
 END IF;
 RETURN NEW;
END;
$record_error$ LANGUAGE plpgsql;

Create a trigger that calls this function whenever a new row is inserted into the hypertable:


CREATE TRIGGER record_error
  BEFORE INSERT ON conditions
  FOR EACH ROW
  EXECUTE PROCEDURE record_error();

All data is inserted into the conditions table, but rows that contain errors are also added to the error_conditions table.

Timescale supports the full range of triggers, including BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE. For more information, see the PostgreSQL docs.

Triggers

Create a trigger

Creating a trigger

Related Content