Defaulting a column to 'now' - Mailing list pgsql-sql
| From | Ken Winter |
|---|---|
| Subject | Defaulting a column to 'now' |
| Date | |
| Msg-id | 003501c600d9$b799a1c0$6603a8c0@kenxp Whole thread Raw |
| Responses |
Re: Defaulting a column to 'now'
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Defaulting a column to 'now' (Bricklen Anderson <banderson@presinet.com>) Re: Defaulting a column to 'now' ("Keith Worthington" <keithw@narrowpathinc.com>) |
| List | pgsql-sql |
<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>