Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE myTable (
    datetime TEXT,
    sys_id INT,
    cputil REAL,
    memfree REAL,
    sessnum INT,
    util_lag REAL,   -- to be calculated AFTER INSERT via TRIGGER
    mem_lag REAL,    -- to be calculated AFTER INSERT via TRIGGER
    util_diff REAL,  -- to be calculated AFTER INSERT via TRIGGER
    mem_diff REAL,/*a comma was missing here*/    -- to be calculated AFTER INSERT via TRIGGER
    util_change TEXT -- to be calculated AFTER INSERT via TRIGGER
);

CREATE TABLE


ALTER TABLE myTable ALTER COLUMN datetime TYPE timestamptz
  USING (datetime::timestamptz);
CREATE INDEX dtidx ON myTable (datetime) 
  --INCLUDE (cputil, memfree)
  WITH (fillfactor=100);
ALTER TABLE myTable CLUSTER ON dtidx;

ALTER TABLE



CREATE INDEX



ALTER TABLE


CREATE OR REPLACE FUNCTION fn_calculate_columns_after_insert()--missing parentheses here
    RETURNS TRIGGER LANGUAGE PLPGSQL AS $f$
BEGIN
    SELECT     cputil, memfree 
    INTO NEW.util_lag, NEW.mem_lag
    FROM myTable 
    --this condition isn't necessary if you always insert more recent data
    --WHERE datetime < NEW.datetime 
    ORDER BY datetime DESC
    LIMIT 1;

    NEW.util_diff := NEW.cputil  - NEW.util_lag;
    NEW.mem_diff  := NEW.memfree - NEW.mem_lag;
    --the three cases explicitly say what you want and when
    --ELSE no longer assumes the same case for 0 and null
    --null is possible if there were any nulls involved earlier
    IF NEW.util_diff > 0 THEN
        NEW.util_change := 'Up';
    ELSIF NEW.util_diff < 0 THEN
        NEW.util_change := 'Down';
    ELSIF NEW.util_diff = 0 THEN
        NEW.util_change := '';
    END IF;--this was missing
    RETURN NEW;
END;--This semicolon is optional because it's immediately followed by closing dollar qoutes.
$f$;--This semicolon was missing. It is not optional, because it ends the whole `create function` statement

CREATE TRIGGER tr_fill_calculated_columns
    BEFORE INSERT ON myTable
    FOR EACH ROW
    EXECUTE FUNCTION fn_calculate_columns_after_insert();

CREATE FUNCTION



CREATE TRIGGER


set datestyle=ISO, YMD;
insert into myTable(datetime,sys_id,cputil,memfree,sessnum)values
 ('2019/05/03 08:06:14',100,0.57,0.51,47)
,('2019/05/03 08:11:14',100,0.47,0.62,43)
,('2019/05/03 08:16:14',100,0.56,0.57,62)
,('2019/05/03 08:21:14',100,0.57,0.56,50)
,('2019/05/03 08:26:14',100,0.35,0.46,43)
,('2019/05/03 08:31:14',100,0.41,0.58,48)
,('2019/05/03 08:36:14',100,0.57,0.35,58)
,('2019/05/03 08:41:14',100,0.41,0.4,58)
,('2019/05/03 08:46:14',100,0.53,0.35,62)
,('2019/05/03 08:51:14',100,0.51,0.6,45)
,('2019/05/03 08:56:14',100,0.32,0.37,47)
,('2019/05/03 09:01:14',100,0.62,0.59,60)
,('2019/05/03 09:06:14',100,0.66,0.72,57)
,('2019/05/03 09:11:14',100,0.54,0.54,44)
,('2019/05/03 09:16:14',100,0.29,0.4,47)
,('2019/05/03 09:21:14',100,0.43,0.68,66)
,('2019/05/03 09:26:14',100,0.49,0.66,65)
,('2019/05/03 09:31:14',100,0.64,0.55,66)
,('2019/05/03 09:36:14',100,0.42,0.6,42)
,('2019/05/03 09:41:14',100,0.55,0.59,63) returning *;

SET




datetime
sys_id
cputil
memfree
sessnum
util_lag
mem_lag
util_diff
mem_diff
util_change


2019-05-03 08:06:14+00
100
0.57
0.51
47
null
null
null
null
null

2019-05-03 08:11:14+00
100
0.47
0.62
43
0.57
0.51
-0.099999994
0.110000014
Down

2019-05-03 08:16:14+00
100
0.56
0.57
62
0.47
0.62
0.09
-0.050000012
Up

2019-05-03 08:21:14+00
100
0.57
0.56
50
0.56
0.57
0.00999999
-0.00999999
Up

2019-05-03 08:26:14+00
100
0.35
0.46
43
0.57
0.56
-0.22
-0.099999994
Down

2019-05-03 08:31:14+00
100
0.41
0.58
48
0.35
0.46
0.060000002
0.119999975
Up

2019-05-03 08:36:14+00
100
0.57
0.35
58
0.41
0.58
0.16
-0.22999999
Up

2019-05-03 08:41:14+00
100
0.41
0.4
58
0.57
0.35
-0.16
0.050000012
Down

2019-05-03 08:46:14+00
100
0.53
0.35
62
0.41
0.4
0.119999975
-0.050000012
Up

2019-05-03 08:51:14+00
100
0.51
0.6
45
0.53
0.35
-0.01999998
0.25000003
Down

2019-05-03 08:56:14+00
100
0.32
0.37
47
0.51
0.6
-0.19
-0.23000002
Down

2019-05-03 09:01:14+00
100
0.62
0.59
60
0.32
0.37
0.3
0.21999997
Up

2019-05-03 09:06:14+00
100
0.66
0.72
57
0.62
0.59
0.04000002
0.13000005
Up

2019-05-03 09:11:14+00
100
0.54
0.54
44
0.66
0.72
-0.120000005
-0.18
Down

2019-05-03 09:16:14+00
100
0.29
0.4
47
0.54
0.54
-0.25000003
-0.14000002
Down

2019-05-03 09:21:14+00
100
0.43
0.68
66
0.29
0.4
0.14000002
0.28
Up

2019-05-03 09:26:14+00
100
0.49
0.66
65
0.43
0.68
0.060000002
-0.01999998
Up

2019-05-03 09:31:14+00
100
0.64
0.55
66
0.49
0.66
0.14999998
-0.110000014
Up

2019-05-03 09:36:14+00
100
0.42
0.6
42
0.64
0.55
-0.22
0.050000012
Down

2019-05-03 09:41:14+00
100
0.55
0.59
63
0.42
0.6
0.13000003
-0.01000005
Up



INSERT 0 20

datetime	sys_id	cputil	memfree	sessnum	util_lag	mem_lag	util_diff	mem_diff	util_change
2019-05-03 08:06:14+00	100	0.57	0.51	47	null	null	null	null	null
2019-05-03 08:11:14+00	100	0.47	0.62	43	0.57	0.51	-0.099999994	0.110000014	Down
2019-05-03 08:16:14+00	100	0.56	0.57	62	0.47	0.62	0.09	-0.050000012	Up
2019-05-03 08:21:14+00	100	0.57	0.56	50	0.56	0.57	0.00999999	-0.00999999	Up
2019-05-03 08:26:14+00	100	0.35	0.46	43	0.57	0.56	-0.22	-0.099999994	Down
2019-05-03 08:31:14+00	100	0.41	0.58	48	0.35	0.46	0.060000002	0.119999975	Up
2019-05-03 08:36:14+00	100	0.57	0.35	58	0.41	0.58	0.16	-0.22999999	Up
2019-05-03 08:41:14+00	100	0.41	0.4	58	0.57	0.35	-0.16	0.050000012	Down
2019-05-03 08:46:14+00	100	0.53	0.35	62	0.41	0.4	0.119999975	-0.050000012	Up
2019-05-03 08:51:14+00	100	0.51	0.6	45	0.53	0.35	-0.01999998	0.25000003	Down
2019-05-03 08:56:14+00	100	0.32	0.37	47	0.51	0.6	-0.19	-0.23000002	Down
2019-05-03 09:01:14+00	100	0.62	0.59	60	0.32	0.37	0.3	0.21999997	Up
2019-05-03 09:06:14+00	100	0.66	0.72	57	0.62	0.59	0.04000002	0.13000005	Up
2019-05-03 09:11:14+00	100	0.54	0.54	44	0.66	0.72	-0.120000005	-0.18	Down
2019-05-03 09:16:14+00	100	0.29	0.4	47	0.54	0.54	-0.25000003	-0.14000002	Down
2019-05-03 09:21:14+00	100	0.43	0.68	66	0.29	0.4	0.14000002	0.28	Up
2019-05-03 09:26:14+00	100	0.49	0.66	65	0.43	0.68	0.060000002	-0.01999998	Up
2019-05-03 09:31:14+00	100	0.64	0.55	66	0.49	0.66	0.14999998	-0.110000014	Up
2019-05-03 09:36:14+00	100	0.42	0.6	42	0.64	0.55	-0.22	0.050000012	Down
2019-05-03 09:41:14+00	100	0.55	0.59	63	0.42	0.6	0.13000003	-0.01000005	Up