How do I automatically update a timestamp in PostgreSQL

Question

I want the code to be able to automatically update the time stamp when a new row is inserted as I can do in MySQL using CURRENT_TIMESTAMP.

How will I be able to achieve this in PostgreSQL?

CREATE TABLE users (
    id serial not null,
    firstname varchar(100),
    middlename varchar(100),
    lastname varchar(100),
    email varchar(200),
    timestamp timestamp
)

Solution 1

To populate the column during insert, use a DEFAULT value:

CREATE TABLE users (
  id serial not null,
  firstname varchar(100),
  middlename varchar(100),
  lastname varchar(100),
  email varchar(200),
  timestamp timestamp default current_timestamp
)

Note that the value for that column can explicitly be overwritten by supplying a value in the INSERT statement. If you want to prevent that you do need a trigger.

You also need a trigger if you need to update that column whenever the row is updated (as mentioned by E.J. Brennan)

Note that using reserved words for column names is usually not a good idea. You should find a different name than timestamp

Solution 2

You'll need to write an insert trigger, and possible an update trigger if you want it to change when the record is changed. This article explains it quite nicely:

http://www.revsys.com/blog/2006/aug/04/automatically-updating-a-timestamp-column-in-postgresql/

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

Apply the trigger like this:

CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE  update_modified_column();

Solution 3

Updating timestamp, only if the values changed

Based on E.J's link and add a if statement from this link (https://stackoverflow.com/a/3084254/1526023)

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.modified = now(); 
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';

Solution 4

Using 'now()' as default value automatically generates time-stamp.

Solution 5

To automatically update the timestamp field in PostgresSQL whenever a new row is inserted, you can set the current_timestamp as its default value:

CREATE TABLE users (
    id serial not null,
    firstname varchar(100),
    middlename varchar(100),
    lastname varchar(100),
    email varchar(200),
    timestamp timestamp default current_timestamp
)

In addition to this, you might want to prevent anyone from updating this field in the future, and this can be done by creating an update trigger and applying it:

CREATE OR REPLACE FUNCTION stop_change_on_timestamp()
  RETURNS trigger AS
$BODY$
BEGIN
  -- always reset the timestamp to the old value ("actual creation time")
  NEW.timestamp := OLD.timestamp;
  RETURN NEW;
END;
$BODY$

CREATE TRIGGER prevent_timestamp_changes
  BEFORE UPDATE
  ON users
  FOR EACH ROW
  EXECUTE PROCEDURE stop_change_on_timestamp();

View more solutions

Comments

[Basil Bourque]

By the way, your data type of timestamp is defined by the SQL spec as an abbreviation of TIMESTAMP WITHOUT TIME ZONE. That is almost certainly not what you want, as explained by Postgres expert David E. Wheeler. The other type, TIMESTAMP WITH TIME ZONE is probably what you want, using any passed time zone offset information to adjust the date-time to UTC (but not actually storing that time zone info despite the type name).

[Basil Bourque]

After having re-learned this yet again, I wrote a detailed blog post about logging the date-time of both a row’s creation and modification using default, function, and trigger. Includes complete example SQL and PL/pgSQL code for use in Postgres.

[Basil Bourque]

Be aware that Postgres has functions to tell you (1) the time of the actual current moment, (2) the time the statement started, and (3) the time the transaction started. The example shown here is start of current transaction. You may or may not want that as opposed to the other two possibilities.

[Basil Bourque]

Good bonus point about avoiding names that collide with reserved words. Note that the SQL spec explicitly promises to never use a trailing underscore as a reserved word. So you can make timestamp into timestamp_. Even better would be a more descriptive name sucha as row_created_.

[Eric]

The first half is not what the OP asked at all, even though the second half referred to other answer(which is correct), but it's still misleading. This should not be accepted as answer.

[Rahly]

Another good thing is to stop using VARCHAR as well. Postgres implements VARCHAR by using a TEXT with a constraint. There is actually a performance penalty in using them vs using straight text. Only use VARCHAR if you REALLY need it constrained to a specific length. Personally, I quote all identifiers in my SQL statements (esp in code) so using reserved words is never an issue. instead of timestamp, I use "timestamp". In general though, I try NOT to use a reserved word, but sometimes it can just be accidental and you don't know it until you actually use it in a specific place.

[a_horse_with_no_name]

@Rahly: no, there is no performance penalty when using varchar instead of text

[Rahly]

Says so in the Postgres documentation of the types. Varchar has extra CPU cycles to check for the constraint, which doesn't happen on TEXT.

[a_horse_with_no_name]

I very much doubt that you can ever notice the difference of "a few extra CPU cycles". For all practical purposes there is no difference in performance (or storage) whatsoever.

[Rahly]

not with a single field, but I've never seen a useful table with a single field. This constraint is compounded by the number of fields. I remember changing all varchar's in a table into text's and gained a 15% writing improvement. Also, small performance penalty is not "NO" performance penalty.

[Pavel Lechev]

This is a more complete solution which would work with initial INSERTs as well as any subsequent UPDATEs (good for data auditing). Granted, the OP only asks for the former.

[Alex Hope O'Connor]

Only for adding values, does not seem to use the default value when updating a record.

[Aldo aldo]

Would creating a trigger for every insertion makes the insertion process expensive? Can this be executed in production environment ?