Re: Defaulting a column to 'now'

On Wed, 14 Dec 2005 13:10:50 -0500, Ken Winter wrote
> How can a column's default be set to 'now', meaning 'now' as of when each
> row is inserted?
> 
> For example, here's a snip of DDL:
> 
> create table personal_data (.
> 
> effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 
> 'now',.
> 
> The problem is, when PostgreSQL processes this DDL, it interprets the 'now'
> as the timestamp when the table is created, so that the tables definition
> reads as if the DDL were:
> 
> effective_date_and_time TIMESTAMP WITH TIME ZONE not null default '
> 2005-12-14 11:00:16.749616-06 ',
> 
> so all of the newly inserted rows get assigned effective_date_and_time 
> = '
> 2005-12-14 11:00:16.749616-06 ', which in addition to being wrong 
> leads to uniqueness constraint violations.
> 
> ~ TIA
> 
> ~ Ken

Ken,

effective_date_and_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
('now'::text)::timestamp(6) with time zone

Kind Regards,
Keith