I wrote:
> I am about to rip out and redo the crufty implementation of default and
> constraint expressions, and I think that I can arrange for UNKNOWN
> constants to remain UNKNOWN when they are stored into the pg_attrdef
> table. This would mean that what gets into pg_attrdef is just the
> unadorned string 'now', and then the coercion of this to a particular
> timestamp will occur when an INSERT statement that uses the default
> is parsed. So the right thing (approximately, anyway) should happen for
> a typical run-of-the-mill INSERT. The wrong thing will still happen
> for an INSERT written in a rule --- its default will be established when
> the rule is created.
I did this, and that's how it works now. Unless we choose to do
something about making C strings and typinput functions fit into the
Postgres type scheme, that's how it will continue to work.
To summarize: in current sources, "default 'now'" works as expected in
simple cases:
play=> create table valplustimestamp (val int, stamp datetime default 'now');
CREATE
play=> insert into valplustimestamp values(1);
INSERT 653323 1
play=> insert into valplustimestamp values(2);
INSERT 653324 1
play=> select * from valplustimestamp;
val|stamp
---+---------------------------- 1|Mon Oct 04 10:58:47 1999 EDT 2|Mon Oct 04 10:58:49 1999 EDT
(2 rows)
but it still has a subtle failure mode:
play=> create view val as select val from valplustimestamp;
CREATE
play=> create rule val_ins as on insert to val do instead
play-> insert into valplustimestamp values(new.val);
CREATE
play=> insert into val values(3);
INSERT 653336 1
play=> insert into val values(4);
INSERT 653337 1
play=> select * from valplustimestamp;
val|stamp
---+---------------------------- 1|Mon Oct 04 10:58:47 1999 EDT 2|Mon Oct 04 10:58:49 1999 EDT 3|Mon Oct 04 10:59:48
1999EDT 4|Mon Oct 04 10:59:48 1999 EDT
(4 rows)
The default value inserted by the rule got frozen when the rule was
parsed, as can be seen by inspecting the back-parsing of the rule:
play=> select * from pg_rules;
tablename|rulename|definition
---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------
val |val_ins |CREATE RULE val_ins AS ON INSERT TO val DO INSTEAD INSERT INTO valplustimestamp (val, stamp) VALUES
(new.val,'Mon Oct 04 10:59:48 1999 EDT'::datetime);
(1 row)
So, we should still recommend "DEFAULT now()" rather than "DEFAULT 'now'"
as the most reliable way of setting up a current-time default.
regards, tom lane
