Thread: Re: [HACKERS] create table and default 'now' problem ?
One way around this bug is to create a SQL function which returns now() and use it as the default value: 1. create function mynow() returns datetime as 'SELECT now()::datetime' LANGUAGE 'SQL'; 2. create table test (a datetime default mynow(), b int4); Now things should work: insert into test (b) values (1); insert into test (b) values (2); select * from test; a |b ----------------------------+- Tue Sep 21 01:05:02 1999 EDT|1 Tue Sep 21 01:05:08 1999 EDT|2 (2 rows) Hope this helps, Mike Mascari (mascarim@yahoo.com) --- Oleg Bartunov <oleg@sai.msu.su> wrote: > Hi, > > how I could create table with datetime field default > to 'now'::text in > a way Jan did in his shoes rule example ? > > If I do: > test=> create table test ( a datetime default 'now', > b int4); > CREATE > test=> insert into test (b) values (1); > INSERT 1677899 1 > test=> insert into test (b) values (2); > INSERT 1677900 1 > test=> select * from test; > a |b > ----------------------------+- > Tue 21 Sep 01:48:27 1999 MSD|1 > Tue 21 Sep 01:48:27 1999 MSD|2 > (2 rows) > > I always get datetime of the moment I created the > table, but I'd like > to have datetime of moment I insert. > > Regards, > > Oleg > __________________________________________________ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
Ignore last...I hadn't clued into the 'same time as table created' part of his message... Thomas...is that not a 'bug' with the datetime/timestamp handling of DEFAULT? *raised eyebrow* On Mon, 20 Sep 1999, Mike Mascari wrote: > One way around this bug is to create a SQL function > which returns now() and use it as the default value: > > 1. create function mynow() returns datetime > as 'SELECT now()::datetime' LANGUAGE 'SQL'; > > 2. create table test (a datetime default mynow(), b > int4); > > Now things should work: > > insert into test (b) values (1); > insert into test (b) values (2); > > select * from test; > a |b > ----------------------------+- > Tue Sep 21 01:05:02 1999 EDT|1 > Tue Sep 21 01:05:08 1999 EDT|2 > (2 rows) > > Hope this helps, > > Mike Mascari > (mascarim@yahoo.com) > > --- Oleg Bartunov <oleg@sai.msu.su> wrote: > > Hi, > > > > how I could create table with datetime field default > > to 'now'::text in > > a way Jan did in his shoes rule example ? > > > > If I do: > > test=> create table test ( a datetime default 'now', > > b int4); > > CREATE > > test=> insert into test (b) values (1); > > INSERT 1677899 1 > > test=> insert into test (b) values (2); > > INSERT 1677900 1 > > test=> select * from test; > > a |b > > ----------------------------+- > > Tue 21 Sep 01:48:27 1999 MSD|1 > > Tue 21 Sep 01:48:27 1999 MSD|2 > > (2 rows) > > > > I always get datetime of the moment I created the > > table, but I'd like > > to have datetime of moment I insert. > > > > Regards, > > > > Oleg > > > __________________________________________________ > Do You Yahoo!? > Bid and sell for free at http://auctions.yahoo.com > > ************ > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
On Mon, 20 Sep 1999, Mike Mascari wrote: > One way around this bug is to create a SQL function > which returns now() and use it as the default value: > > 1. create function mynow() returns datetime > as 'SELECT now()::datetime' LANGUAGE 'SQL'; > > 2. create table test (a datetime default mynow(), b > int4); > > Now things should work: > > insert into test (b) values (1); > insert into test (b) values (2); > > select * from test; > a |b > ----------------------------+- > Tue Sep 21 01:05:02 1999 EDT|1 > Tue Sep 21 01:05:08 1999 EDT|2 > (2 rows) > > Hope this helps, Why the 'create function'? hardware=> create table test_table ( a int4, ts datetime default 'now' ); CREATE hardware=> insert into test_table values ( 1 ) ; INSERT 115445 1 hardware=> select * from test_table; a|ts -+---------------------------- 1|Tue Sep 21 02:00:50 1999 EDT (1 row) > > Mike Mascari > (mascarim@yahoo.com) > > --- Oleg Bartunov <oleg@sai.msu.su> wrote: > > Hi, > > > > how I could create table with datetime field default > > to 'now'::text in > > a way Jan did in his shoes rule example ? > > > > If I do: > > test=> create table test ( a datetime default 'now', > > b int4); > > CREATE > > test=> insert into test (b) values (1); > > INSERT 1677899 1 > > test=> insert into test (b) values (2); > > INSERT 1677900 1 > > test=> select * from test; > > a |b > > ----------------------------+- > > Tue 21 Sep 01:48:27 1999 MSD|1 > > Tue 21 Sep 01:48:27 1999 MSD|2 > > (2 rows) > > > > I always get datetime of the moment I created the > > table, but I'd like > > to have datetime of moment I insert. > > > > Regards, > > > > Oleg > > > __________________________________________________ > Do You Yahoo!? > Bid and sell for free at http://auctions.yahoo.com > > ************ > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
> > how I could create table with datetime field default
> > to 'now'::text in a way Jan did in his shoes rule example ?
> > If I do:
> > test=> create table test ( a datetime default 'now',
> > b int4);
> > CREATE
> > I always get datetime of the moment I created the
> > table, but I'd like to have datetime of moment I insert.
> One way around this bug is to create a SQL function
> which returns now() and use it as the default value:
Not necessary, though this does work well. A simpler way is to
actually do what Oleg asks about:
create table test ( a datetime default text 'now',...)
or
create table test ( a datetime default 'now'::text,...)
which should force the string to *stay* as a string, rather than
getting converted to a date value when the table is created. Once it
is forced to be a string, then it will be converted at insert time
instead.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Tue, 21 Sep 1999, Thomas Lockhart wrote: > Date: Tue, 21 Sep 1999 06:14:12 +0000 > From: Thomas Lockhart <lockhart@alumni.caltech.edu> > To: Mike Mascari <mascarim@yahoo.com> > Cc: Oleg Bartunov <oleg@sai.msu.su>, pgsql-hackers@postgreSQL.org > Subject: Re: [HACKERS] create table and default 'now' problem ? > > > > how I could create table with datetime field default > > > to 'now'::text in a way Jan did in his shoes rule example ? > > > If I do: > > > test=> create table test ( a datetime default 'now', > > > b int4); > > > CREATE > > > I always get datetime of the moment I created the > > > table, but I'd like to have datetime of moment I insert. > > One way around this bug is to create a SQL function > > which returns now() and use it as the default value: > > Not necessary, though this does work well. A simpler way is to > actually do what Oleg asks about: > > create table test ( a datetime default text 'now',...) > This works ! Thanks > or > > create table test ( a datetime default 'now'::text,...) Parser complains: ERROR: parser: parse error at or near "'" Does this considered as a bug or feature ? Oleg > > which should force the string to *stay* as a string, rather than > getting converted to a date value when the table is created. Once it > is forced to be a string, then it will be converted at insert time > instead. > > - Thomas > > -- > Thomas Lockhart lockhart@alumni.caltech.edu > South Pasadena, California > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
> Why the 'create function'?
> hardware=> insert into test_table values ( 1 ) ;
> hardware=> select * from test_table;
> 1|Tue Sep 21 02:00:50 1999 EDT
Right. And if you run the insert again, you'll see the exact same time
inserted. But if you force 'now' to be a true string type (rather than
leaving it unspecified) then the evaluation will happen at insert
time.
The behavior is "correct" for most values of most types, but falls
down when a seemingly constant value, like a fixed string N-O-W,
actually is not a constant but rather something which changes value
depending on when the query runs. In the long run, we need to have a
new attribute associated with data types which tells whether constants
have that nature (most won't). In the meantime, this is a feature, and
has been since Vadim (?) implemented DEFAULT ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
>>>>>> how I could create table with datetime field default
>>>>>> to 'now'::text in a way Jan did in his shoes rule example ?
A couple of comments on this thread:
1. Seems to me that the easy, reliable way is just to use the
now() function --- you don't have to make one, it's built in:
create table test ( a datetime default now(), b int);
This avoids all the issues about when constants get coerced, and
probably ought to be what we recommend to newbies. However,
this is certainly a workaround for an existing bug.
2. I believe that most of the problem with premature constant coercion
in default values is coming from the bizarre way that default values get
entered into the database. StoreAttrDefault essentially converts the
parsed default-value tree back to text, constructs a SELECT statement
using the text, parses that, and examines the resulting parsetree.
Yech. If it were done carefully it might work, but it's not; the
reverse parser does not do quoting carefully, does not do type coercion
carefully, and fails to handle large parts of the expression syntax at
all. (I've ranted about this before ... check the pghackers archives.)
I have a to-do list item to rip all that code out and do it over again
right. Might or might not get to it for 6.6 --- does someone else want
to tackle it?
3. Yes, this is a bug too:
>> create table test ( a datetime default 'now'::text,...)
> Parser complains:
> ERROR: parser: parse error at or near "'"
> Does this considered as a bug or feature ?
See above --- reverse-parsing of this construct is wrong. I have
no intention of fixing the reverse parser; I want to get rid of it
entirely.
regards, tom lane
Thank you Tom for explanation. It's not very bothered me as far as I have
many workarounds suggested in mailing list. But I wondering because
'now'::text works as expected when I create view
create view www_auth as select a.account as user_name, a.password, b.nick as group_name from users a,
resourcesb, privilege_user_map c where a.auth_id = c.auth_id and b.res_id = c.res_id and
(a.account_valid_untilis null or a.account_valid_until > datetime('now'::text)) and
c.perm_id= 1;
Regards, Oleg
On Tue, 21 Sep 1999, Tom Lane wrote:
> Date: Tue, 21 Sep 1999 09:40:40 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
> pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] create table and default 'now' problem ?
>
> >>>>>> how I could create table with datetime field default
> >>>>>> to 'now'::text in a way Jan did in his shoes rule example ?
>
> A couple of comments on this thread:
>
> 1. Seems to me that the easy, reliable way is just to use the
> now() function --- you don't have to make one, it's built in:
>
> create table test ( a datetime default now(), b int);
>
> This avoids all the issues about when constants get coerced, and
> probably ought to be what we recommend to newbies. However,
> this is certainly a workaround for an existing bug.
>
> 2. I believe that most of the problem with premature constant coercion
> in default values is coming from the bizarre way that default values get
> entered into the database. StoreAttrDefault essentially converts the
> parsed default-value tree back to text, constructs a SELECT statement
> using the text, parses that, and examines the resulting parsetree.
> Yech. If it were done carefully it might work, but it's not; the
> reverse parser does not do quoting carefully, does not do type coercion
> carefully, and fails to handle large parts of the expression syntax at
> all. (I've ranted about this before ... check the pghackers archives.)
>
> I have a to-do list item to rip all that code out and do it over again
> right. Might or might not get to it for 6.6 --- does someone else want
> to tackle it?
>
> 3. Yes, this is a bug too:
>
> >> create table test ( a datetime default 'now'::text,...)
> > Parser complains:
> > ERROR: parser: parse error at or near "'"
> > Does this considered as a bug or feature ?
>
> See above --- reverse-parsing of this construct is wrong. I have
> no intention of fixing the reverse parser; I want to get rid of it
> entirely.
>
> regards, tom lane
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> Thank you Tom for explanation. It's not very bothered me as far as I have
> many workarounds suggested in mailing list. But I wondering because
> 'now'::text works as expected when I create view
Yes, it's just the context of a DEFAULT expression that has these
problems. (Actually, it looks like constraints --- CHECK() expressions
--- are handled in the same bogus way, but we don't seem to get as many
gripes about them...)
regards, tom lane