You have a user table in Postgres:
CREATE SCHEMA tutorial; CREATE TABLE tutorial.crud_users ( id INT NOT NULL, username VARCHAR NOT NULL, CONSTRAINT crud_table_pkey PRIMARY KEY (id) );
You insert new users and update usernames for existing users. Update is a destructive operation that overwrites data, so how do you keep track of what usernames a particular id has had over time?
Use a trigger
You can use triggers in Postgres to remember the historical values of your rows.
First create a new table to store the history, which you can think of as a logical stream of updates to the "crud_user" table. Each row in "stream_users" corresponds to a current or historical state of a row in "crud_users".
CREATE TABLE tutorial.stream_users ( id INT, created_ts TIMESTAMP, username VARCHAR NOT NULL, CONSTRAINT stream_table_pkey PRIMARY KEY (id, created_ts) );
Next, create a trigger on "crud_users".
CREATE OR REPLACE FUNCTION crud_users_insert_trigger_fnc() RETURNS trigger AS $$ BEGIN INSERT INTO "tutorial"."stream_users" ( "id", "created_ts", "username") VALUES(NEW."id", now(), NEW."username"); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER user_insert_trigger AFTER INSERT OR UPDATE ON "tutorial"."crud_users" FOR EACH ROW EXECUTE PROCEDURE crud_users_insert_trigger_fnc();
The trigger inserts a new row in "stream_users" every time you insert or update a row in "crud_users". Thus, we keep track of all historical and current values of users.
Try it out
select * from tutorial.crud_users; -- should be empty select * from tutorial.stream_users; -- should be empty insert into tutorial.crud_users(id, username) values (1, 'joe'); update tutorial.crud_users set username='joe69' where id=1;
Now, If you delete a user from "crud_users" we will not insert anything into the "stream_users" table. To improve the design, we could add a state column to stream_users, which reflects the kind of change, e.g. created, updated or deleted.