How do I automatically update a timestamp in PostgreSQL

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

  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

Leave a Comment