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