On 15/01/2015 Pavel Stehule wrote about implementing a dead simple history table using the hstore type.

On Friday evening I wanted to copy this almost line for line switching the hstore type for jsonb , but I counldn’t really see how to replicate the update part so simply without creating a delete operator. Once that operator has been created it appears to work:

CREATE TABLE test(a int, b int, c int); CREATE TABLE history( event_time timestamp(2), executed_by text, origin_value jsonb, new_value jsonb ); CREATE OR REPLACE FUNCTION history_insert() RETURNS TRIGGER AS $$ BEGIN INSERT INTO history(event_time, executed_by, new_value) VALUES(CURRENT_TIMESTAMP, SESSION_USER, row_to_json(NEW)::jsonb); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION history_delete() RETURNS TRIGGER AS $$ BEGIN INSERT INTO history(event_time, executed_by, origin_value) VALUES(CURRENT_TIMESTAMP, SESSION_USER, row_to_json(OLD)::jsonb); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS $$ DECLARE js_new jsonb := row_to_json(NEW)::jsonb; js_old jsonb := row_to_json(OLD)::jsonb; BEGIN INSERT INTO history(event_time, executed_by, origin_value, new_value) VALUES(CURRENT_TIMESTAMP, SESSION_USER, js_old - js_new, js_new - js_old); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER test_history_insert AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE history_insert(); CREATE TRIGGER test_history_delete AFTER DELETE ON test FOR EACH ROW EXECUTE PROCEDURE history_delete(); CREATE TRIGGER test_history_update AFTER UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE history_update();

Result:

INSERT INTO test VALUES(1000, 1001, 1002); UPDATE test SET a = 10, c = 20; DELETE FROM test; postgres=# table history; event_time | executed_by | origin_value | new_value ------------------------+-------------+-------------------------------+----------------------------------- 2015-01-19 13:12:54.26 | glyn | | {"a": 1000, "b": 1001, "c": 1002} 2015-01-19 13:12:54.31 | glyn | {"a": 1000, "c": 1002} | {"a": 10, "c": 20} 2015-01-19 13:12:54.94 | glyn | {"a": 10, "b": 1001, "c": 20} | (3 rows)

Tested on PostgreSQL 9.4