Обсуждение: 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