Обсуждение: Defaulting a column to 'now'

"Ken Winter" <ken@sunward.org> writes:
> How can a column's default be set to 'now', meaning 'now' as of when each
> row is inserted?

You need a function, not a literal constant.  The SQL-spec way isCURRENT_TIMESTAMP
(which is a function, despite the spec's weird idea that it should be
spelled without parentheses); the traditional Postgres way isnow()

Either way only sets an insertion default, though.  If you want to
enforce a correct value on insertion, or change the value when the
row is UPDATEd, you need to use a trigger.
        regards, tom lane

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',…
> 

try with now(), instead of now
...
effective_date_and_time TIMESTAMPTZ not null default 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

Thanks, Tom (also Keith Worthington and Bricklen Anderson).  That works.

~ Ken

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, December 14, 2005 1:15 PM
> To: Ken Winter
> Cc: PostgreSQL pg-sql list
> Subject: Re: [SQL] Defaulting a column to 'now'
> 
> "Ken Winter" <ken@sunward.org> writes:
> > How can a column's default be set to 'now', meaning 'now' as of when
> each
> > row is inserted?
> 
> You need a function, not a literal constant.  The SQL-spec way is
>     CURRENT_TIMESTAMP
> (which is a function, despite the spec's weird idea that it should be
> spelled without parentheses); the traditional Postgres way is
>     now()
> 
> Either way only sets an insertion default, though.  If you want to
> enforce a correct value on insertion, or change the value when the
> row is UPDATEd, you need to use a trigger.
> 
>             regards, tom lane