pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: example of insert timestamp
7+ messages / 5 participants
[nested] [flat]

* example of insert timestamp
@ 2008-02-26 01:21 David Bear <david.bear@asu.edu>
  2008-02-26 01:19 ` Re: example of insert timestamp Gregory Williamson <Gregory.Williamson@digitalglobe.com>
  2008-02-26 01:21 ` Re: example of insert timestamp Aaron Bono <aaron.bono@aranya.com>
  2008-02-26 01:26 ` Re: example of insert timestamp Steve Holdoway <steve.holdoway@firetrust.com>
  0 siblings, 3 replies; 7+ messages in thread

From: David Bear @ 2008-02-26 01:21 UTC (permalink / raw)
  To: pgsql-admin

I've been reading about triggers on insert and found the page at 
http://www.postgresql.org/docs/8.1/interactive/triggers.html with some sample 
code in the comments.

I'm thinking what I want can't really be this involved.

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.

Otherwise, is there any sample code that would should be how to do this? The 
sample on the page above looks like overkill.

-- 

David Bear
College of Public Programs/ASU
411 N Central, Phoenix, AZ 85004



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: example of insert timestamp
  2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
@ 2008-02-26 01:19 ` Gregory Williamson <Gregory.Williamson@digitalglobe.com>
  2008-02-26 02:05   ` Re: example of insert timestamp Jamie A Lawrence <postgres@jal.org>
  2 siblings, 1 reply; 7+ messages in thread

From: Gregory Williamson @ 2008-02-26 01:19 UTC (permalink / raw)
  To: David Bear <david.bear@asu.edu>; 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.)

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: example of insert timestamp
  2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
  2008-02-26 01:19 ` Re: example of insert timestamp Gregory Williamson <Gregory.Williamson@digitalglobe.com>
@ 2008-02-26 02:05   ` Jamie A Lawrence <postgres@jal.org>
  2008-02-26 02:41     ` Re: example of insert timestamp Aaron Bono <aaron.bono@aranya.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Jamie A Lawrence @ 2008-02-26 02:05 UTC (permalink / raw)
  To: Gregory Williamson <Gregory.Williamson@digitalglobe.com>; +Cc: David Bear <david.bear@asu.edu>; pgsql-admin


On Feb 25, 2008, at 8:19 PM, Gregory Williamson wrote:
>
> 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);
>

This is what I do. For many tables, I also add a mod_time column with  
an ON UPDATE trigger similar to the one previously posted. Even when  
not needed for the table itself, in development, it can be very handy  
to compare dodgy inserts/updates with commit logs when bug hunting...

-j



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: example of insert timestamp
  2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
  2008-02-26 01:19 ` Re: example of insert timestamp Gregory Williamson <Gregory.Williamson@digitalglobe.com>
  2008-02-26 02:05   ` Re: example of insert timestamp Jamie A Lawrence <postgres@jal.org>
@ 2008-02-26 02:41     ` Aaron Bono <aaron.bono@aranya.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Aaron Bono @ 2008-02-26 02:41 UTC (permalink / raw)
  To: Jamie A Lawrence <postgres@jal.org>; +Cc: Gregory Williamson <Gregory.Williamson@digitalglobe.com>; David Bear <david.bear@asu.edu>; pgsql-admin

On Mon, Feb 25, 2008 at 8:05 PM, Jamie A Lawrence <postgres@jal.org> wrote:

>
> On Feb 25, 2008, at 8:19 PM, Gregory Williamson wrote:
> >
> > 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);
> >
>
> This is what I do. For many tables, I also add a mod_time column with
> an ON UPDATE trigger similar to the one previously posted. Even when
> not needed for the table itself, in development, it can be very handy
> to compare dodgy inserts/updates with commit logs when bug hunting...
>
>
>
I know you asked for something more like a create timestamp, but if you also
want a modify timestamp, that would definitely need a trigger:

CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF
opaque AS
'
BEGIN
    -- if a trigger insert or update operation occurs
    IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
        -- assigns the current timestamp
        -- into the modify date and time column
        NEW.modify_dt := now();

        -- displays the new row on an insert/update
        RETURN NEW;
    END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE
    ON "public"."mytable" FOR EACH ROW
    EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();

This function is very generic and can be used for any and all of your tables
so you don't have to create a function for each table as long as the column
name is the same.

-Aaron

-- 
==================================================================
  Aaron Bono
  Aranya Software Technologies, Inc.
  http://www.aranya.com
  http://codeelixir.com
==================================================================

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: example of insert timestamp
  2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
@ 2008-02-26 01:21 ` Aaron Bono <aaron.bono@aranya.com>
  2008-02-26 01:46   ` Re: example of insert timestamp David Bear <david.bear@asu.edu>
  2 siblings, 1 reply; 7+ messages in thread

From: Aaron Bono @ 2008-02-26 01:21 UTC (permalink / raw)
  To: David Bear <david.bear@asu.edu>; +Cc: pgsql-admin

On Mon, Feb 25, 2008 at 7:21 PM, David Bear <david.bear@asu.edu> wrote:

> I've been reading about triggers on insert and found the page at
> http://www.postgresql.org/docs/8.1/interactive/triggers.html with some
> sample
> code in the comments.
>
> I'm thinking what I want can't really be this involved.
>
> 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.
>
> Otherwise, is there any sample code that would should be how to do this?
> The
> sample on the page above looks like overkill.<http://www.postgresql.org/docs/faq;
>

In your create table statement:

"create_dt" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL

-- 
==================================================================
  Aaron Bono
  Aranya Software Technologies, Inc.
  http://www.aranya.com
  http://codeelixir.com
==================================================================

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: example of insert timestamp
  2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
  2008-02-26 01:21 ` Re: example of insert timestamp Aaron Bono <aaron.bono@aranya.com>
@ 2008-02-26 01:46   ` David Bear <david.bear@asu.edu>
  0 siblings, 0 replies; 7+ messages in thread

From: David Bear @ 2008-02-26 01:46 UTC (permalink / raw)
  To: Aaron Bono <aaron.bono@aranya.com>; +Cc: pgsql-admin

On Monday 25 February 2008 18:21, Aaron Bono wrote:
> On Mon, Feb 25, 2008 at 7:21 PM, David Bear <david.bear@asu.edu> wrote:
> > I've been reading about triggers on insert and found the page at
> > http://www.postgresql.org/docs/8.1/interactive/triggers.html with some
> > sample
> > code in the comments.
> >
> > I'm thinking what I want can't really be this involved.
> >
> > 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.
> >
> > Otherwise, is there any sample code that would should be how to do this?
> > The
> > sample on the page above looks like
> > overkill.<http://www.postgresql.org/docs/faq;
>
> In your create table statement:
>
> "create_dt" TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL

Thanks. I knew it must be easier. This is exactly what I want.

-- 

David Bear
College of Public Programs/ASU
411 N Central, Phoenix, AZ 85004



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: example of insert timestamp
  2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
@ 2008-02-26 01:26 ` Steve Holdoway <steve.holdoway@firetrust.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Steve Holdoway @ 2008-02-26 01:26 UTC (permalink / raw)
  To: pgsql-admin

On Mon, 25 Feb 2008 18:21:19 -0700
David Bear <david.bear@asu.edu> wrote:

> I've been reading about triggers on insert and found the page at 
> http://www.postgresql.org/docs/8.1/interactive/triggers.html with some sample 
> code in the comments.
> 
> I'm thinking what I want can't really be this involved.
> 
> 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.
> 
> Otherwise, is there any sample code that would should be how to do this? The 
> sample on the page above looks like overkill.
> 
> -- 
> 
> David Bear
> College of Public Programs/ASU
> 411 N Central, Phoenix, AZ 85004
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
Here's a quick example which sets column dateadded to now on insert... can't get much simpler!


create or replace function surbl.AddUrl()
returns TRIGGER AS
$$
BEGIN
-- Must have an url to add
IF ( NEW.url IS NULL )
THEN
	RETURN NULL;
END IF;
NEW.dateadded := now();
RETURN NEW;
END;
$$ language 'plpgsql';

drop trigger addurl_trig ON surbl.rawdata;
create trigger addurl_trig BEFORE INSERT ON surbl.rawdata
FOR EACH ROW
EXECUTE PROCEDURE surbl.AddUrl();

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2008-02-26 02:41 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-26 01:21 example of insert timestamp David Bear <david.bear@asu.edu>
2008-02-26 01:19 ` Gregory Williamson <Gregory.Williamson@digitalglobe.com>
2008-02-26 02:05   ` Jamie A Lawrence <postgres@jal.org>
2008-02-26 02:41     ` Aaron Bono <aaron.bono@aranya.com>
2008-02-26 01:21 ` Aaron Bono <aaron.bono@aranya.com>
2008-02-26 01:46   ` David Bear <david.bear@asu.edu>
2008-02-26 01:26 ` Steve Holdoway <steve.holdoway@firetrust.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox