Thread: Wrong manual info?
From the 7.2 manual, functions-datetime.html: All the date/time datatypes also accept the special literal value now to specify the current date and time. Thus, the following three all return the same result: SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; Note: You do not want to use the third form when specifying a DEFAULT value while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used! The first two forms will not be evaluated until the default value is used, because they are function calls. Thus they will give the desired behavior of defaulting to the time of row insertion. As far as I can tell, 'now' works perfectly as a dynamic column DEFAULT. It uses insert time, NOT table creation time Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> As far as I can tell, 'now' works perfectly as a dynamic column DEFAULT. It
> uses insert time, NOT table creation time
Hmm. The manual is correct as written, because it warns against writing
TIMESTAMP 'now', which indeed does not work. In current sources I get:
regression=# create table foo (f1 int, f2 timestamp default TIMESTAMP 'now',
regression(# f3 timestamp default 'now');
CREATE
regression=# insert into foo values(1);
INSERT 139644 1
regression=# insert into foo values(2);
INSERT 139645 1
regression=# insert into foo values(3);
INSERT 139646 1
regression=# select * from foo;
f1 | f2 | f3
----+------------------------+------------------------
1 | 2001-10-08 10:36:02-04 | 2001-10-08 10:36:07-04
2 | 2001-10-08 10:36:02-04 | 2001-10-08 10:36:08-04
3 | 2001-10-08 10:36:02-04 | 2001-10-08 10:36:10-04
(3 rows)
regression=# \d foo
Table "foo"
Column | Type | Modifiers
--------+-----------------------------+-----------------------------------------------
f1 | integer |
f2 | timestamp(0) with time zone | default '2001-10-08 10:36:02-04'::timestamptz
f3 | timestamp(0) with time zone | default 'now'
regression=# select * from pg_attrdef where adrelid =
regression-# (select oid from pg_class where relname = 'foo');
adrelid | adnum | adbin
| adsrc
---------+-------+--------------------------------------------------------------------------------------------------------------------------+---------------------------------------
139642 | 2 | { CONST :consttype 1184 :constlen 8 :constbyval false :constisnull false :constvalue 8 [ 65 -118
-93-78 -112 0 0 0 ] } | '2001-10-08 10:36:02-04'::timestamptz
139642 | 3 | { CONST :consttype 705 :constlen -1 :constbyval false :constisnull false :constvalue 7 [ 0 0 0 7
110111 119 ] } | 'now'
(2 rows)
So TIMESTAMP 'now' does get coerced to a timestamp constant on sight,
which is what I would expect. I find it rather surprising that the
unknown-type literal isn't getting coerced during CREATE TABLE too.
After looking at the code, I see that this is a deliberate hack to
make the world safe for DEFAULT 'now' --- see catalog/heap.c around line
1630 in current sources. However, I think this is an ugly backwards-
compatibility hack, rather than something the manual should recommend
as preferred practice. So I think the docs are okay as is.
regards, tom lane
> So TIMESTAMP 'now' does get coerced to a timestamp constant on sight, > which is what I would expect. I find it rather surprising that the > unknown-type literal isn't getting coerced during CREATE TABLE too. > After looking at the code, I see that this is a deliberate hack to > make the world safe for DEFAULT 'now' --- see catalog/heap.c around line > 1630 in current sources. However, I think this is an ugly backwards- > compatibility hack, rather than something the manual should recommend > as preferred practice. So I think the docs are okay as is. Fair enough. It just had me quite confused. Maybe it would be worth putting in a qualifier that says that DEFAULT 'now' is ok? Another thing, why is there a funtional form of 'now' called now(), but not a functional form of 'today' called today()? (7.1.3) Chris
Christopher Kings-Lynne writes: > Another thing, why is there a funtional form of 'now' called now(), but not > a functional form of 'today' called today()? (7.1.3) Most of these are legacy and/or compatibility and/or convenience things. 'today' is now called CURRENT_DATE and 'now' is called CURRENT_TIMESTAMP, plus or minus a type cast. The now() function is also there for ODBC compliance. To make things worse, ODBC also adds (or should add) CURDATE() and CURRENT_DATE() and other such redundant things. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter