Skip site navigation (1) Skip section navigation (2)

Re: example of insert timestamp

From: "Gregory Williamson" <Gregory(dot)Williamson(at)digitalglobe(dot)com>
To: "David Bear" <david(dot)bear(at)asu(dot)edu>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: example of insert timestamp
Date: 2008-02-26 01:19:56
Message-ID: 8B319E5A30FF4A48BE7EEAAF609DB233015E33E0@COMAIL01.digitalglobe.com (view raw or flat)
Thread:
Lists: pgsql-admin
David --

> 
> I want to have a table with a timestamp field that automatically gets the 
> value of now() on insert. The timestamp will never be updated. I assume I 
> need to create a trigger to do this. If there is an easier way, please 
> advise.

Perhaps, make the column so it has a default value of now, land then insert without specifying the timestamp column so it gets the default value:

create table foo (q_i_time timestamp with time zone not null default now(), someval int);

CREATE TABLE
billing=# insert into foo (someval) values (22);
INSERT 0 1
billing=# insert into foo (someval) values (26);
INSERT 0 1
billing=# insert into foo (someval) values (1);
INSERT 0 1
billing=# select * from foo;
           q_i_time            | someval
-------------------------------+---------
 2008-02-25 17:23:03.247619-08 |      22
 2008-02-25 17:23:07.43922-08  |      26
 2008-02-25 17:23:10.111189-08 |       1
(3 rows)

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

In response to

Responses

pgsql-admin by date

Next:From: David BearDate: 2008-02-26 01:21:19
Subject: example of insert timestamp
Previous:From: Aaron BonoDate: 2008-02-25 21:19:45
Subject: Re: System in Recovery Mode

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group