How do you most easily create a timestamp column that updates to the
current time on every UPDATE statement? I know you can use triggers, but
I thought there was a default value that did this for you. 'current'
does not seem appropriate, nor have I made it work (see below for my
example).
http://www.postgresql.org/docs/postgres/x1137.htm :
"'now' is resolved when the value is inserted, 'current' is
resolved everytime the value is retrieved."
Any clues?
Regards,
Ed Loehr
BTW, here's the sequence I used to conclude 'current' doesn't seem to
work:
DROP TABLE foo;
CREATE TABLE foo (
note VARCHAR,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated TIMESTAMP NOT NULL DEFAULT 'current'
);
SELECT now();
INSERT INTO foo (note) VALUES ('Default timestamp test record');
SELECT created FROM foo;
SELECT now();
SELECT * FROM foo;
-- Wait a few seconds for the time to clearly change seconds...
SELECT now();
UPDATE foo SET note = 'Did the update_time change with an UPDATE query?';
SELECT now();
SELECT * FROM foo;
SELECT updated FROM foo;
