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
)

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](http://justatheory.com/computers/databases/postgresql/use-timestamptz.html). 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](http://en.wikipedia.org/wiki/Coordinated_Universal_Time) (but not actually storing that time zone info despite the type name). — Basil Bourque, Oct 01 '14 at 21:16
After having re-learned this yet again, I wrote a [detailed blog post](http://crafted-software.blogspot.com/2014/10/track-date-time-of-row-creation.html) 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, Oct 02 '14 at 22:09

score 299 · Accepted Answer · edited May 23 '17 at 12:10

299

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

edited May 23 '17 at 12:10

Community

1
1

answered Mar 04 '12 at 16:23

26

Be aware that Postgres has [functions](http://www.postgresql.org/docs/current/static/functions-datetime.html) 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 Oct 01 '14 at 21:06
10

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_`. – Basil Bourque Oct 01 '14 at 21:08
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. – Eric Sep 03 '16 at 07:44
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. – Rahly Jun 21 '17 at 15:41
@Rahly: no, there is no performance penalty when using `varchar` instead of `text` – Jun 21 '17 at 15:47
2

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. – Rahly Jun 21 '17 at 15:50
1

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. – Jun 21 '17 at 15:52
5

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. – Rahly Jun 21 '17 at 16:18

score 188 · Answer 2 · edited Apr 22 '17 at 00:50

188

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();

edited Apr 22 '17 at 00:50

luckydonald

5,976
4
38
58

answered Mar 04 '12 at 16:16

E.J. Brennan

45,870
7
88
116

1

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. – Pavel Lechev Jun 04 '18 at 10:37
1

Would creating a trigger for every insertion makes the insertion process expensive? Can this be executed in production environment ? – Aldo aldo Jun 27 '22 at 04:23

score 93 · Answer 3 · edited May 23 '17 at 12:34

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';

score 19 · Answer 4 · answered Jun 06 '15 at 19:29

19

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

answered Jun 06 '15 at 19:29

Parichay Mo

225
2
2

16

Only for adding values, does not seem to use the default value when updating a record. – Alex Hope O'Connor Sep 14 '20 at 20:48
In addition to that, it is a "default" value so can be overwriten, which might be a problem for audit purposes. – comte Feb 09 '23 at 16:33

score 7 · Answer 5 · answered Oct 11 '21 at 18:55

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();

score 1 · Answer 6 · answered Oct 28 '21 at 13:45

For update and Liquibase YAML:

databaseChangeLog:
  - changeSet:
      id: CREATE FUNCTION set_now_to_timestamp
      author: Konstantin Chvilyov
      changes:
        - sql:
            sql: CREATE OR REPLACE FUNCTION set_now_to_timestamp() RETURNS TRIGGER LANGUAGE plpgsql AS 'BEGIN NEW.timestamp = NOW(); RETURN NEW; END;'

  - changeSet:
      id: CREATE TRIGGER update_users_set_now_to_timestamp
      author: Konstantin Chvilyov
      changes:
        - sql:
            sql: CREATE TRIGGER update_users_set_now_to_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE set_now_to_timestamp();

How do I automatically update a timestamp in PostgreSQL

6 Answers6

Linked