From the 7.2 manual, functions-datetime.html:
All the date/time datatypes also accept the special literal value now to
specify the current date and time. Thus, the following three all return the
same result:
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
Note: You do not want to use the third form when specifying a DEFAULT value
while creating a table. The system will convert now to a timestamp as soon
as the constant is parsed, so that when the default value is needed, the
time of the table creation would be used! The first two forms will not be
evaluated until the default value is used, because they are function calls.
Thus they will give the desired behavior of defaulting to the time of row
insertion.
As far as I can tell, 'now' works perfectly as a dynamic column DEFAULT. It
uses insert time, NOT table creation time
Chris