Like MySQL, PostgreSQL doesn’t have different column for Datetime and Timestamp.
You can visit this MySQL article here:
In this post, I am sharing small demonstration to automatically update a timestamp column when a row is updated in the table of the PostgreSQL.
In the big transactional system like banking system, we always require to know last updated datetime of each and every transaction and rows.
We have also common practice to create last_modified_datetime column in table.
We can also update this column using application and we can also update this column using user defined function.
But we can also create Trigger to perform this operation and this automatic approach is good.
Below is a small demonstration by creating update Trigger:
Create a sample table:
CREATE TABLE tbl_EmployeeDetails
,EmpName CHARACTER VARYING(50)
,EmpDOB TIMESTAMP WITHOUT TIME ZONE
,LastUpdatedDateTime TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
,CONSTRAINT pk_tbl_EmployeeDetails_EmpID PRIMARY KEY(EmpID)
Insert a sample data for EmpName and EmpDOB column:
INSERT INTO tbl_EmployeeDetails
Create a Trigger function:
CREATE OR REPLACE FUNCTION trg_fn_tbl_EmployeeDetails_LastUpdatedDateTime()
RETURNS TRIGGER AS $$
NEW.LastUpdatedDateTime = NOW();
$$ language 'plpgsql';
Create an UPDATE TRIGGER:
CREATE TRIGGER trg_update_tbl_EmployeeDetails BEFORE UPDATE
ON tbl_EmployeeDetails FOR EACH ROW EXECUTE PROCEDURE
Update one record and check LastUpdatedDateTime:
UPDATE tbl_EmployeeDetails SET EmpDOB = '1991-09-12' WHERE EmpID=4;
SELECT *FROM tbl_EmployeeDetails;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.