How do I automatically update a timestamp in PostgreSQL

database postgresql timestamp

Aaron · Mar 4, 2012 · Viewed 150.5k times · Source

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
)

Answer

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

How do I automatically update a timestamp in PostgreSQL

Answer

Related questions