Defaulting a column to 'now' - Mailing 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>