Demonstrating timestamp auto update on Postgresql, using Triggers.

postgresql_auto_update_timestamp.sql

-- Function which will update 'updated_at' columnd of any table that has it.
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
	NEW.updated_at = NOW();
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- Table with 'updated_at' column, with DEFAULT NOW() for new records
-- NOTE: TIMESTAMPTZ is an abbreviation for TIMESTAMP WITH TIME ZONE
-- https://www.postgresql.org/docs/12/datatype-datetime.html
CREATE TABLE todos (
	id SERIAL NOT NULL PRIMARY KEY,
	content TEXT,
	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
	completed_at TIMESTAMPTZ
);

-- Trigger which will call the function before an update is performed on 'todos'
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON todos
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

-- Initial row/tuple for testing purposes.
INSERT INTO todos (content) VALUES('Retake Stanford course.');

-- Checking row/tuple that was added.
SELECT * FROM todos;

-- Update record to see if 'updated_at' was indeed updated
UPDATE todos
SET content = 'Retake Stanford course and do exercises (constraints + triggers)'
WHERE id = 1;

-- Check timestamp update
SELECT created_at, updated_at FROM todos;