Re: [SQL] timestamps

David Rugge <davidrugge(at)mindspring(dot)com> writes:
> I created a table using this statement:
> create table timestamptest (creation_date timestamp default text 'now')
> But I get the creation date of the table instead of the date of the
> transaction. This is the format suggested in the manual to workaround the
> default value being set at table creation, but it doesn't work!

Try "default now()".

The documentation recommending "default text 'now'" is bogus, IMHO.
That method depends on a particular set of interacting behaviors in the
parser and the table-default-creation code, and at least some of those
behaviors were arguably bugs. They were also data-type-dependent ---
I believe "default text 'now'" does work in 6.5.* for a DATETIME column,
but not for a TIMESTAMP column, because of slight differences in the
sets of available operators for the two datatypes.

FYI, any of these variants should give the result you want in future
releases. But now() is the only one that I think can be counted on
to work in current and obsolete Postgres versions too.

regards, tom lane