MySQL versus PostgreSQL: Adding a 'Last Modified Time' Column to a Table

Posted in Databases and Programming on Friday, the 14th of March, 2008.

This is the second post here detailing my ongoing adventures with PostgreSQL. This time I had a requirement to add a "timestamp" column to a table. The point of this being to allow us to track the "last modified" time of a row, without requiring that the application code manage the timestamp itself.

There's a lot of reasons why you might wish to do this. In this case it was to simplify syncing the data into a data warehouse. More specifically, to allow the DBA to easily identify rows which have changed since the last import.

Having done this a couple of times in MySQL, I assumed that the process would be straightforward. I should know better by now!

Comments

Posted by Daniel on Tuesday, the 10th of February, 2009.

Thanks! This was helpful.

Posted by fansipans on Friday, the 10th of April, 2009.

Unless I'm missing something, all of the trigger contortions above can be obviated by setting the column's default value to "now()".

One of the nice features of PostgreSQL is that you can have a function call as the default, and it will be evaluated on INSERT.

test=# create table foo (bar integer, baz timestamp default now());
CREATE TABLE
test=# insert into foo (bar) values (12);
INSERT 0 1
test=# select * from foo;
bar | baz
-----+----------------------------
12 | 2009-04-10 09:09:52.942531
(1 row)

Posted by fansipans on Friday, the 10th of April, 2009.

Oh dur, I didn't notice you needed it on UPDATE - right. Then you'd need a trigger ;)

Posted by Kevin F on Thursday, the 28th of May, 2009.

As an extension to the discussion, mySQL only supports one such timestamp per table. Thus, if your requirements are to track an INSERT date and an UPDATE date column, for instance, then you would also have to use mysql triggers, as in postgres. It's a strange restriction. beware.

Posted by westcoast coder on Friday, the 24th of July, 2009.

Thanks for this post. It saved my day as I've been scratching to do so since it was so easy to do in MySQL and struggling to do in postgres

Posted by Daniele Pignedoli on Tuesday, the 28th of July, 2009.

Great article, i found it very usefull... anyway i hope the postgres team will soon create a automatic function for this case (as the serial aka auto_increment)

Posted by Ben Maden on Saturday, the 26th of September, 2009.

Great article... I was looking for a way to do this across all tables. I'm sure there must be a way.... not found it but did find this...

Installed the SPI module
http://www.postgresql.org/docs/current/static/contrib-spi.html

psql -U postgres db_name < ./contrib/moddatetime.sql
(needed yum install postgres-contrib first)

So any table can now have the following trigger added....

CREATE TRIGGER table_update BEFORE UPDATE
ON table_name FOR EACH ROW EXECUTE PROCEDURE
moddatetime('updated_at');

Similar to your code but you can now pass in the update field name as required... just in case you don't have a convention.

Thanks again for the great post.

cheers,

Ben

Posted by Ludwig Kniprath on Monday, the 10th of May, 2010.

Thanks a lot,
as extension you can add
NEW.last_update_by = session_user;
to the function above to get the equivalent of mySQLs
ON UPDATE CURRENT_USER;
(assumed the fieldname is last_update_by)

Posted by Iceblue on Wednesday, the 7th of September, 2011.

This trigger do not give the same result as mysql timestamp. With this solution its not possible to set the timestamp field itself manually without messing it up. Mysql timestamp allowed that...I really dont see how to achieve this in postgresql

Posted by Simon Harris on Wednesday, the 7th of September, 2011.

Thanks, Iceblue. That's interesting if true.

Really though, the whole point of a timestamp column is that you don't set it yourself, but I acknowledge that MySQL allows this, for better or worse.

Posted by Chris Angelico on Friday, the 2nd of December, 2011.

Yes, it's easier in MySQL. But Postgres gives the huge advantage of flexibility. This is something I see in quite a few places; it's easier to set up a dynamic web site using MySQL, but Postgres lets you use its tools in more ways. 99% of situations are covered by MySQL's options, but the other 1% are actually quite fiddly to work with; Postgres covers everything, but forces you to be a bit more verbose to do it.

I would like a more concise trigger notation though. Forcing triggers to be stored procedures is a bit annoying.

Posted by alias on Thursday, the 28th of March, 2013.

With regard to Iceblue's comment about the procedure not allowing a purposely-set value in the timestamp column, the procedure can be rigged up to allow for this:

create or replace function update_timestamp()
returns trigger as $$
begin
if new.lastmodified is null or new.lastmodified = old.lastmodified then
new.lastmodified = now();
end if;
return new;
end;
$$ language 'plpgsql';

Of course, this assumes you're not trying to explicitly set the existing value back into the field, so there is still a minor uncovered corner case to consider.

Enter your comment: