Auto update timestamp on update

Question

I have created a table where its following code is:

CREATE TABLE products (
    id SERIAL, 
    store_id INTEGER NOT NULL,
    url TEXT UNIQUE,
    visible BOOLEAN,
    added_date timestamp without time zone NOT NULL DEFAULT NOW(),
    PRIMARY KEY(id, store_id)
);

and together with Peewee and I did figure out that on update, the timestamp doesn't automatically updates whenever I do an update on a row by doing e.g.

class Products(Model):
    id = IntegerField(column_name='id')
    store_id = TextField(column_name='store_id')
    url = TextField(column_name='url')
    visible = BooleanField(column_name='visible')
    added_date = TimestampField(column_name='added_date')

    class Meta:
        database = postgres_pool
        db_table = "products"

    @classmethod
    def on_exists(cls, pageData):
        try:
            if exists := cls.select().where((cls.store_id == Stores.store_id) & (cls.url == pageData.url)).exists():
                # On update execution it should refresh the added_time to current time
                cls.update(visible=False).where((cls.store_id == Stores.store_id) & (cls.url == pageData.url)).execute()
            return exists
        except Products.DoesNotExist:
            return None

However my problem is that everytime I am using this command and successfully update. the added_time timestamp does not seem to update its timestamp. My question is, how can it automatically update its timestamp on update?

Answer 1

This is a job for a trigger:

CREATE FUNCTION upd_ts() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.added_date = current_timestamp;
   RETURN NEW;
END;$$;

CREATE TRIGGER upd_ts BEFORE UPDATE ON products
   FOR EACH ROW EXECUTE PROCEDURE upd_ts();

The trigger will fire before the rows are modified and change the row about to be inserted.

See the documentation for all the details.