Postgres Pro
Downloads
Home   >   mailing lists

Thread: Defaulting a column to 'now'

Defaulting a column to 'now'

From
"Ken Winter"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">How can a column’s default be set to ‘now’, meaning ‘now’ as of when each row is
inserted?</span></font><pclass="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">For example, here’s a snip of DDL:</span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">create table personal_data (…</span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana">effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 'now',…</span></font><p
class="MsoNormal"><fontface="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">The problem is, when PostgreSQL processes this DDL, it interprets the ‘now’ as the timestamp when
thetable is created, so that the tables definition reads as if the DDL were:</span></font><p class="MsoNormal"><font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">effective_date_and_time TIMESTAMP WITH TIME ZONE not null default '</span></font> <font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana">2005-12-14 11:00:16.749616-06 ',</span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">so all of the newly inserted rows get assigned effective_date_and_time = '</span></font> <font
face="Verdana"size="2"><span style="font-size:10.0pt; 
font-family:Verdana">2005-12-14 11:00:16.749616-06 ', which in addition to being wrong leads to uniqueness constraint
violations.</span></font><pclass="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">~ TIA</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana">~ Ken</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font></div>

Re: Defaulting a column to 'now'

From
Tom Lane
Date:
"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

Re: Defaulting a column to 'now'

From
Bricklen Anderson
Date:
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()...

Re: Defaulting a column to 'now'

From
"Keith Worthington"
Date:
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

Re: Defaulting a column to 'now'

From
"Ken Winter"
Date:
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