add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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