This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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 on how to update a timestamp column automatically when a row is updated in the table of the PostgreSQL.
In the big transactional system like the banking system, we always require last updated datetime of every transaction and row.
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 the 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:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_EmployeeDetails ( EmpID SERIAL ,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:
|
1 2 3 4 5 6 7 |
INSERT INTO tbl_EmployeeDetails (EmpName,EmpDOB) VALUES ('Anvesh','1988-01-26') ,('Neevan','2000-06-29') ,('Martin','1985-08-04') ,('Jeny','1990-09-12'); |
Create a Trigger function:
|
1 2 3 4 5 6 7 |
CREATE OR REPLACE FUNCTION trg_fn_tbl_EmployeeDetails_LastUpdatedDateTime() RETURNS TRIGGER AS $$ BEGIN NEW.LastUpdatedDateTime = NOW(); RETURN NEW; END; $$ language 'plpgsql'; |
Create an UPDATE TRIGGER:
|
1 2 3 |
CREATE TRIGGER trg_update_tbl_EmployeeDetails BEFORE UPDATE ON tbl_EmployeeDetails FOR EACH ROW EXECUTE PROCEDURE trg_fn_tbl_EmployeeDetails_LastUpdatedDateTime(); |
Update a record and check LastUpdatedDateTime:
|
1 |
UPDATE tbl_EmployeeDetails SET EmpDOB = '1991-09-12' WHERE EmpID=4; |
The Result:
|
1 |
SELECT *FROM tbl_EmployeeDetails; |
