Re: I was spoiled by the MySQL timestamp field

"Matthew Nuzum" <cobalt@bearfruit.org> writes:
> The difference is when now() gets interpreted into a date.  Someone
> please correct me if I'm wrong...

> If you create a table at 15:00 Jan 26, 2003 and you use an unquoted
> now() as the default value for a field, then each record will have it's
> default value as 15:00 Jan 26, 2003.

You're wrong...

now() is a function call and will not be folded into a constant.

AFAIR, the only case that does get folded to a constant is

    ... mycol timestamp default timestamp 'now'

since "timestamp 'now'" is Postgres' notation for a literal constant of
a specific datatype (timestamp in this case).  The string 'now' is
immediately fed to the timestamp datatype's input converter, and behold
it produces the current time, which is then stored as a timestamp
constant.

The notation 'now()' that various people have suggested is in fact
invalid, being not a function call but a literal --- but it seems that
the timestamp input converter is sloppy about detecting trailing garbage
in its input string.  You should get a "Bad timestamp external
representation" error from it, but at the moment you don't.

You can easily check the behavior for yourself rather than relying on
other people's assertions.  For example:

regression=# create table t1 (f1 timestamp default now(),
regression(# f2 timestamp default 'now',
regression(# f3 timestamp default timestamp 'now');
CREATE TABLE
regression=# \d t1
                                            Table "public.t1"
 Column |            Type             |                             Modifiers

--------+-----------------------------+-------------------------------------------------------------------
 f1     | timestamp without time zone | default now()
 f2     | timestamp without time zone | default 'now'
 f3     | timestamp without time zone | default '2003-01-26 16:51:11.246954'::timestamp without time zone

and even more directly:

regression=# insert into t1 default values;
INSERT 1003028 1
regression=# insert into t1 default values;
INSERT 1003029 1
regression=# select * from t1;
             f1             |             f2             |             f3
----------------------------+----------------------------+----------------------------
 2003-01-26 16:58:13.173561 | 2003-01-26 16:58:13.173561 | 2003-01-26 16:51:11.246954
 2003-01-26 16:58:14.323162 | 2003-01-26 16:58:14.323162 | 2003-01-26 16:51:11.246954
(2 rows)


(BTW, the reason 'now' without "timestamp" in front works is that this
is not a timestamp literal but a text literal, which will be coerced
to timestamp at runtime.)

            regards, tom lane