
Now I thought this was discussed recently and this:

create table foo(
x       int,
y       datetime default current_time);

would put the current date and time into y whenever a new record was
inserted.  It appears to give the date and time the stupid table was
created.  Is it me or is something broke?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
  # include <std/disclaimers.h>       Have you seen http://www.pop4.net?
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================



Tue, 19 Oct 1999 22:05:33 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
current_time?
Vince Vielhaber <vev@michvhf.com> writes:
> Now I thought this was discussed recently and this:
> create table foo(
> x       int,
> y       datetime default current_time);
> would put the current date and time into y whenever a new record was
> inserted.  It appears to give the date and time the stupid table was
> created.  Is it me or is something broke?

The behavior for this was changed very recently.  Since current sources
refuse the above:

regression=> create table foo(
regression-> x       int,
regression-> y       datetime default current_time);
ERROR:  Attribute 'y' is of type 'datetime' but default expression is of type 'time'
        You will need to rewrite or cast the expression

I am guessing you are trying it with 6.5.*, where indeed you will likely
get the time of table creation.  Recommended approach is
	y  datetime default now()
which works the way you want in all Postgres versions AFAIK.

Next question is whether current sources are broken to refuse the above.
Since I get

regression=> create table zz (x datetime);
CREATE
regression=> insert into zz values(current_time);
ERROR:  Attribute 'x' is of type 'datetime' but expression is of type 'time'
        You will need to rewrite or cast the expression

it seems I managed to make default-expression handling consistent
with the rest of the system, but that doesn't necessarily mean this
behavior is desirable...  Thomas, what say you?

			regards, tom lane

Wed, 20 Oct 1999 00:52:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] current_time? 
On Wed, 20 Oct 1999, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > Now I thought this was discussed recently and this:
> > create table foo(
> > x       int,
> > y       datetime default current_time);
> > would put the current date and time into y whenever a new record was
> > inserted.  It appears to give the date and time the stupid table was
> > created.  Is it me or is something broke?
> 
> The behavior for this was changed very recently.  Since current sources
> refuse the above:
> 
> regression=> create table foo(
> regression-> x       int,
> regression-> y       datetime default current_time);
> ERROR:  Attribute 'y' is of type 'datetime' but default expression is of type 'time'
>         You will need to rewrite or cast the expression
> 
> I am guessing you are trying it with 6.5.*, where indeed you will likely
> get the time of table creation.  Recommended approach is
> 	y  datetime default now()
> which works the way you want in all Postgres versions AFAIK.

This works.  I had tried something earlier (during the thread a couple
weeks back)  DEFAULT TEXT 'now'   which didn't work at all for me.  A
little playing and I just now figured out why it didn't work..  When I
tried that I had y as a text field - which only put a 'now' in it so I
was avoiding using now under the assumption that it wouldn't work.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
  # include <std/disclaimers.h>       Have you seen http://www.pop4.net?
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Wed, 20 Oct 1999 06:47:36 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] current_time? 