Postgresql Timestamps

Anon

I'm trying to find a way to update a timestamp field when a record is updated in postgresql automaticly, unless the timestamp field is specified in the query.

So far I've got this, but it doens't work:
CREATE RULE table_timestamp_rule AS ON INSERT TO table WHERE new.lastupdate is null DO UPDATE table set lastupdate = timestamp('now') WHERE table.oid = new.oid;

Any ideas?

Thanks

Anon

As i understand it, timestamp fields should be updated automatically any time a row is updated. Am I missing something? I'd simply refer to the postgres docs - I'm 99.99% certain that timestamp fields always update whenever the row updates - that's what they are for.

Anon

If the field is of type timestamp (a PostgreSQL type) then no, it's not going to be automatically updated anyting the record is updated. It's the user's responsibility to update fields, nothing is going to happen automatically...

UPDATE <table> SET timstamp_field = 'now',int4_field=4 WHERE whatever = whatever

That'll update the timestamp field but only because you're specifically telling it to.

UPDATE <table> SET int4_fiels=4 WHERE whatever = whatever --- isn't going to ( and shouldn't) touch the timestamp field.

Anon

You're right - this is a case where postgres handles it very differently than mysql.

Interesting! Anyway, I like the automation that MySQL provides here.

Anon

I agree with the advantage of automation. now if we could just get mysql to borrow the idea of a "serial" type like postgresql uses for not null unique auto-incrementing primary keys (or whatever that huge line of words is to make one.)

Anon

Perhaps as another type of field, sure.. Surely you wouldn't want a timestamp field that you could never control though!

I have several database drive applications that hold timestamps for many things (such as when the record was added) -- I really wouldn't want that to change every time the record was updated..

Anon

Of course. The new type would likely be built on the older timestamp, but we could call it something unixish and geeky like ctime or mtime or something for change/mod time.

Anon

The automation is exacly what I'm trying to reproduce in posgresql.

I want a field that will update to 'now' if that field is not implicitly set in an update statement.

I feel my orginal rule is very close if you change the rule to 'update'.

So, does anyone know of a way to implement this rule in postgresql? I'm looking for the sql to create it or ideas on constructing it.

Tom

Anon

It's trivially easy to write a trigger to update a timestamp whenever a row is inserted or updated (trival because that's one of the supplied examples of triggers!) But the hour is late here, and alas my brain fails me when I ask it to remember whether or not it's feasible to detect an explict value being supplied from within the trigger in order to skip the update in that case.

Anon

I've searched through http://www.postgresql.org/docs/postgres over and over and not found the example you refer to here. Will you post the exact url please?