Postgres Upsert using Triggers

Problem

Time series data taken for 5 data points: heartrate, beatsmissed, ibi, spo2, steps.
HTTP API provides data point for each API call (eg GET /$tenant/$user/heartrate/<time-range>/).
All the 5 data points need to be merged into a single Postgres table row. We use a Postgres ON CONFLICT DO UPDATE trigger.
Bulk import data using CSV files.

Creating the PG Table

CREATE DATABASE IF NOT EXISTS omnyk;
use omnyk;

DROP TABLE IF EXISTS data_points;

CREATE TABLE data_points (
  tenantid    TEXT                         NOT NULL,
  userid      UUID                         NOT NULL,
  ringid      UUID                         NOT NULL,
  time        TIMESTAMP(3) NOT NULL,

  heartrate   SMALLINT,
  beatsmissed BOOLEAN,
  ibi         SMALLINT,
  spo2        SMALLINT,
  steps       SMALLINT,

  UNIQUE(tenantid, userid, ringid, TIME)
);

Postgres UPSERT Trigger


-- ETL for migration from KairosDB to Postgres
-- We read data point (column) at a time using the API and insert into postgres, the
-- below trigger adds an ON CONFLICT DO UPDATE to ensure each column is updated in the
-- appropriate row.

CREATE OR REPLACE FUNCTION data_points_ignore_dups() RETURNS TRIGGER AS $
    BEGIN
      IF NEW.heartrate IS NOT NULL THEN
          INSERT INTO data_points(tenantid, userid, ringid, time, heartrate)
            VALUES (NEW.tenantid, NEW.userid, NEW.ringid, NEW.time, NEW.heartrate)
            ON CONFLICT (tenantid, userid, ringid, time) DO UPDATE
            SET heartrate = (EXCLUDED.heartrate);
          RETURN NULL;
      END IF;

      IF NEW.beatsmissed IS NOT NULL THEN
          INSERT INTO data_points(tenantid, userid, ringid, time, beatsmissed)
            VALUES (NEW.tenantid, NEW.userid, NEW.ringid, NEW.time, NEW.beatsmissed)
            ON CONFLICT (tenantid, userid, ringid, time) DO UPDATE
            SET beatsmissed = (EXCLUDED.beatsmissed);
          RETURN NULL;
      END IF;

      IF NEW.ibi IS NOT NULL THEN
          INSERT INTO data_points(tenantid, userid, ringid, time, ibi)
            VALUES (NEW.tenantid, NEW.userid, NEW.ringid, NEW.time, NEW.ibi)
            ON CONFLICT (tenantid, userid, ringid, time) DO UPDATE
            SET ibi = (EXCLUDED.ibi);
          RETURN NULL;
      END IF;
      IF NEW.spo2 IS NOT NULL THEN
          INSERT INTO data_points(tenantid, userid, ringid, time, spo2)
            VALUES (NEW.tenantid, NEW.userid, NEW.ringid, NEW.time, NEW.spo2)
            ON CONFLICT (tenantid, userid, ringid, time) DO UPDATE
            SET spo2 = (EXCLUDED.spo2);
          RETURN NULL;
      END IF;

      IF NEW.steps IS NOT NULL THEN
          INSERT INTO data_points(tenantid, userid, ringid, time, steps)
            VALUES (NEW.tenantid, NEW.userid, NEW.ringid, NEW.time, NEW.steps)
            ON CONFLICT (tenantid, userid, ringid, time) DO UPDATE
            SET steps = (EXCLUDED.steps);
          RETURN NULL;
      END IF;

      RETURN NULL;
    END;
$ LANGUAGE plpgsql;

-- Attach the trigger to INSERTs. pg_trigger_depth() avoids trigger recursion.

CREATE TRIGGER data_points_insert_trigger BEFORE INSERT ON data_points
    FOR EACH ROW
        WHEN (pg_trigger_depth() < 1)
        EXECUTE PROCEDURE  data_points_ignore_dups();

#postgres #upsert #triggers