Skip to content

Instantly share code, notes, and snippets.

@brianmed
Created November 7, 2014 09:09
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save brianmed/0e73292da11940a95b98 to your computer and use it in GitHub Desktop.
Save brianmed/0e73292da11940a95b98 to your computer and use it in GitHub Desktop.
Auto update timestamp in postgres
[bpm@dev000] c:/opt>cat sql
BEGIN;
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated = now();
RETURN NEW;
END;
$$ language 'plpgsql';
COMMIT;
BEGIN;
CREATE TABLE account(
id serial not null PRIMARY KEY,
email VARCHAR(128) NOT NULL UNIQUE,
username VARCHAR(30) NOT NULL UNIQUE,
password VARCHAR(128) NOT NULL,
updated timestamp default CURRENT_TIMESTAMP,
inserted timestamp default CURRENT_TIMESTAMP
);
CREATE TRIGGER user_timestamp BEFORE INSERT OR UPDATE ON account
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
COMMIT;
[bpm@dev000] c:/opt>sudo -u postgres psql db
psql (8.4.20)
Type "help" for help.
db=# insert into account (email, username, password) values ('a@a.com', 'a', 'abc');
INSERT 0 1
db=# select * from account;
id | email | username | password | updated | inserted
----+---------+----------+----------+----------------------------+----------------------------
3 | a@a.com | a | abc | 2014-11-07 09:06:46.526592 | 2014-11-07 09:06:46.526592
(1 row)
db=# update account set username = 'b' where id = 3;
UPDATE 1
db=# select * from account;
id | email | username | password | updated | inserted
----+---------+----------+----------+----------------------------+----------------------------
3 | a@a.com | b | abc | 2014-11-07 09:06:53.671612 | 2014-11-07 09:06:46.526592
(1 row)
db=#
@kenle06
Copy link

kenle06 commented Sep 11, 2021

This is not good. it will loop cycle

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment