From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-docs@postgresql.org
Subject: Re: Wrong manual info?
Date: Mon, 08 Oct 2001 10:50:53 -0400
Message-ID: <691.1002552653@sss.pgh.pa.us> (raw)
In-Reply-To: <ECEHIKNFIMMECLEBJFIGKECJCCAA.chriskl@familyhealth.com.au>
References: <ECEHIKNFIMMECLEBJFIGKECJCCAA.chriskl@familyhealth.com.au>
"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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au
Subject: Re: Wrong manual info?
In-Reply-To: <691.1002552653@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox