current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 16 down vote favorite
11

In Mysql, we can execute this where it updates the changetimestamp every time the row is changed.

create table ab (
  id int, 
  changeTimestamp timestamp 
    NOT NULL 
    default CURRENT_TIMESTAMP 
    on update CURRENT_TIMESTAMP 
);

Is there something similar to do the above in Postgresql?

share|improve this question
    
afaik not as easy in PostgreSQL where you need a trigger: pointbeing.net/weblog/2008/03/… –  bernie Jun 24 '09 at 0:58

1 Answer 1

up vote 45 down vote

Create a function that updates the changetimestamp column of a table like so:

CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.changetimestamp = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

Create a trigger on the table that calls the update_changetimestamp_column() function whenever an update occurs like so:

    CREATE TRIGGER update_ab_changetimestamp BEFORE UPDATE
    ON ab FOR EACH ROW EXECUTE PROCEDURE 
    update_changetimestamp_column();
share|improve this answer
    
So, there is no other way of doing what I want except through a trigger? Because I would like to implement 'on update timestamp' for all of my tables possibly 300+. And I'm thinking that creating triggers might cause some performance issues. –  bichonfrise74 Jun 24 '09 at 1:10
3  
As far as I know this is the standard way to do this in postgresql. When you write "on update current_timestamp" in mysql, it creates a trigger on the table in the background. The difference is that you're writing the trigger here manually instead of having it written for you. –  Charles Ma Jun 24 '09 at 1:38
3  
There is practically no performance penalty - at least in any sensible database. –  user80168 Jun 24 '09 at 7:44
    
You'll initially need to load the language with CREATE LANGUAGE plpgsql; That's only needed once. –  Daniel Winterstein Nov 14 '11 at 14:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.