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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq