PostgreSQL update timestamp when row is updated
In PostgreSQL, if you want to set current timestamp as default value, you can simply keep a column's default expression as 'now()'. However, by default there is no function defined to update the timestamp when a particular row (or multiple rows) are updated.
In such scenario, you may create your custom method and trigger it using PostgreSQL's Triggers. Following snippet will make it more clear:
Here, we are creating a new method, 'method_get_updated_at()'
CREATE OR REPLACE FUNCTION method_get_updated_at() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.<column_name> = now();
RETURN NEW;
END;
$$;
Once it is created, this is the snippet for triggering it:
CREATE TRIGGER trigger_<column_name>
BEFORE UPDATE ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE method_get_updated_at();
If you want to delete a Trigger, you can use this simple command:
DROP TRIGGER IF EXISTS trigger_<column_name> ON <table_name>
Note: Please update the table_name and column_name accordingly and execute the code for your database. Also, note that, some web frameworks like Rails, manage such columns(created_at, updated_at) automatically.
In such scenario, you may create your custom method and trigger it using PostgreSQL's Triggers. Following snippet will make it more clear:
Here, we are creating a new method, 'method_get_updated_at()'
CREATE OR REPLACE FUNCTION method_get_updated_at() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.<column_name> = now();
RETURN NEW;
END;
$$;
Once it is created, this is the snippet for triggering it:
CREATE TRIGGER trigger_<column_name>
BEFORE UPDATE ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE method_get_updated_at();
If you want to delete a Trigger, you can use this simple command:
DROP TRIGGER IF EXISTS trigger_<column_name> ON <table_name>
Note: Please update the table_name and column_name accordingly and execute the code for your database. Also, note that, some web frameworks like Rails, manage such columns(created_at, updated_at) automatically.