Re: I was spoiled by the MySQL timestamp field

"Matthew Nuzum" <cobalt(at)bearfruit(dot)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