Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f98EpDn80203 for ; Mon, 8 Oct 2001 10:51:13 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f98EorI00694; Mon, 8 Oct 2001 10:50:53 -0400 (EDT) To: "Christopher Kings-Lynne" cc: pgsql-docs@postgresql.org Subject: Re: Wrong manual info? In-reply-to: References: Comments: In-reply-to "Christopher Kings-Lynne" message dated "Mon, 08 Oct 2001 16:03:06 +0800" Date: Mon, 08 Oct 2001 10:50:53 -0400 Message-ID: <691.1002552653@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/29 X-Sequence-Number: 1043 "Christopher Kings-Lynne" 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 110 111 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