Postgres Pro
Downloads
Home   >   mailing lists

Thread: Default timestamp value

Default timestamp value

From
"Rob S."
Date:
As a precursor to this question, I read:
CREATE TABLEDate/Time Types (and corresponding input/output)Date/Time Functions

...but I still don't see how to have the default value of a timestamp to be
the time at which the individual record is inserted.  I just get the time I
created the table.  Specifically, what to put where the '?' is at.

... "TimeDate" TIMESTAMP DEFAULT ? ...

I tried 'now' and 'current' but it just makes the default value the time
when I create the table, and 'current' gives me the word 'current' back in
psql, so I imagine I'm not taking that in quite the right way =)  I can see
why it does this for both of these, althought in the docs, it says current
is current time, deferred, but I dunno what that means.

Any help or pointers to a nice resource?  IMHO, the PG docs are a great
reference, but not much in the way of terrific examples =)

- r

Re: Default timestamp value

From
Thomas Behr
Date:
"Rob S." wrote:
> 
> As a precursor to this question, I read:
> 
>         CREATE TABLE
>         Date/Time Types (and corresponding input/output)
>         Date/Time Functions
> 
> ...but I still don't see how to have the default value of a timestamp to be
> the time at which the individual record is inserted.  I just get the time I
> created the table.  Specifically, what to put where the '?' is at.
> 
> ... "TimeDate" TIMESTAMP DEFAULT ? ...

Hallo Rob,
an example, which works by me [PostgreSQL 6.4.2 on i686-pc-linux-gnu,
compiled by gcc 2.7.2.]:

CREATE TABLE "session" ( "zeitpunkt" timestamp DEFAULT now ( ) NOT NULL, "sid" int4 NOT NULL, "nummer" int4 NOT NULL,
"status"character varying NOT NULL, "host" inet);
 

Ade

Thomas

> 
> I tried 'now' and 'current' but it just makes the default value the time
> when I create the table, and 'current' gives me the word 'current' back in
> psql, so I imagine I'm not taking that in quite the right way =)  I can see
> why it does this for both of these, althought in the docs, it says current
> is current time, deferred, but I dunno what that means.
> 
> Any help or pointers to a nice resource?  IMHO, the PG docs are a great
> reference, but not much in the way of terrific examples =)
> 
> - r

Re: Default timestamp value

From
Tom Lane
Date:
"Rob S." <rslifka@home.com> writes:
> ...but I still don't see how to have the default value of a timestamp to be
> the time at which the individual record is inserted.  I just get the time I
> created the table.  Specifically, what to put where the '?' is at.

> ... "TimeDate" TIMESTAMP DEFAULT ? ...

In 7.0 either "'now'" or "now()" should work, eg

regression=# create table foo (f1 int, f2 timestamp default now());
CREATE
regression=# insert into foo values(1);
INSERT 395192 1
regression=# insert into foo values(2);
INSERT 395193 1
regression=# select * from foo;f1 |           f2
----+------------------------ 1 | 2000-06-05 11:15:25-04 2 | 2000-06-05 11:15:28-04
(2 rows)

Versions before 7.0 are not entirely consistent about this, but I
believe the explicit function call now() will work the way you want
in any version.

BTW, this *is* covered in the FAQ, seehttp://www.postgresql.org/docs/faq-english.html#4.22
        regards, tom lane

Re: Default timestamp value

From
Jeff Hoffmann
Date:
Tom Lane wrote:
> 
> "Rob S." <rslifka@home.com> writes:
> > ...but I still don't see how to have the default value of a timestamp to be
> > the time at which the individual record is inserted.  I just get the time I
> > created the table.  

> Versions before 7.0 are not entirely consistent about this, but I
> believe the explicit function call now() will work the way you want
> in any version.

you can also use the value CURRENT_TIMESTAMP, which is standard SQL. 
you have to be careful about using 'now' as a default, though.  i have a
tendancy to put type qualifications on most of the things i write ever
since i had problems with getting an index to be used on an int2 field. 
the key to the faq is that you can't put a type qualification after the
'now', otherwise it won't work.  so in 7.0, if you create a table like
any of these:

create table foo (f1 int, f2 timestamp default now());
create table foo (f1 int, f2 timestamp default 'now');
create table foo (f1 int, f2 timestamp default CURRENT_TIMESTAMP);

it will work.  if you do it like this, it won't:

create table foo (f1 int, f2 timestamp default 'now'::datetime);
create table foo (f1 int, f2 timestamp default 'now'::timestamp);

it will give you the time that the table was created.  it's just a
little gotcha that might frustrate people in a similar situation.

jeff

RE: Default timestamp value

From
"Rob S."
Date:
Thanks very much everyone!

> BTW, this *is* covered in the FAQ, see
>     http://www.postgresql.org/docs/faq-english.html#4.22

Ahh, but I did look at a FAQ! I just went back and checked the date, I guess
I shouldn't have just skimmed down the page: Last updated: Mon Oct 14
08:05:23 EDT 1996.  That's the last time I search for a FAQ before checking
the PG page. ;)

Thanks again everybody =)

- r

SQL92 and Traditional

From
Toshihide Nakamura
Date:
Hello,

I have a question.  I am seeing lots of SQL92-compared-to-Traditional-SQL.
What is a definition of "Traditional"?  Is it SQL89? 86?  Or anything
before 92?  It seems like postgresql supports both, but which is it better
to write?  

Thanks,


Tony