I want the code to be able to automatically update the time stamp when a new row is inserted as I can do in MySQL using CURRENT_TIMESTAMP.
How will I be able to achieve this in PostgreSQL?
CREATE TABLE users (
id serial not null,
firstname varchar(100),
middlename varchar(100),
lastname varchar(100),
email varchar(200),
timestamp timestamp
)
This question is related to
database
postgresql
timestamp
Updating timestamp, only if the values changed
Based on E.J's link and add a if statement from this link (https://stackoverflow.com/a/3084254/1526023)
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
NEW.modified = now();
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$ language 'plpgsql';
You'll need to write an insert trigger, and possible an update trigger if you want it to change when the record is changed. This article explains it quite nicely:
http://www.revsys.com/blog/2006/aug/04/automatically-updating-a-timestamp-column-in-postgresql/
CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.modified = now(); RETURN NEW; END; $$ language 'plpgsql';
Apply the trigger like this:
CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
Using 'now()' as default value automatically generates time-stamp.
Source: Stackoverflow.com