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

User · Accepted 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

User · Answer

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

User · Answer

Using  now    as default value automatically generates time-stamp

User · Answer

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

[database] How do I automatically update a timestamp in PostgreSQL

Examples related to database

Examples related to postgresql

Examples related to timestamp