Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 7D477475A3D for ; Tue, 26 Feb 2002 10:56:37 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1QFtfS29992; Tue, 26 Feb 2002 10:55:41 -0500 (EST) To: Christopher Kings-Lynne Cc: Matteo Beccati , pgsql-sql@postgresql.org Subject: Re: Timestamp output In-reply-to: <20020226223623.L56253-100000@houston.familyhealth.com.au> References: <20020226223623.L56253-100000@houston.familyhealth.com.au> Comments: In-reply-to Christopher Kings-Lynne message dated "Tue, 26 Feb 2002 22:39:17 +0800" Date: Tue, 26 Feb 2002 10:55:41 -0500 Message-ID: <29989.1014738941@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/397 X-Sequence-Number: 6819 Christopher Kings-Lynne writes: > Although I know of this problem, I would also be interested in the fix. > I know that you can declare a column of type timestamp(0) to get the old > format, but how do you change an existing column? Officially, it's not supported. Unofficially, you can always hack pg_attribute.atttypmod, which is where precision info is stored. Observe the following example: regression=# create table foo (f1 timestamp, f2 timestamp(0)); CREATE regression=# \d foo Table "foo" Column | Type | Modifiers --------+-----------------------------+----------- f1 | timestamp with time zone | f2 | timestamp(0) with time zone | regression=# select * from pg_attribute where attrelid = regression-# (select oid from pg_class where relname = 'foo') regression-# and attnum > 0; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attisset | attalign | attnotnull | atthasdef ----------+---------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+----------+------------+----------- 146285 | f1 | 1184 | 10 | 8 | 1 | 0 | -1 | -1 | f | p | f | d | f | f 146285 | f2 | 1184 | 10 | 8 | 2 | 0 | -1 | 0 | f | p | f | d | f | f (2 rows) Comparing the atttypmod values, we see that -1 implies "no precision restriction" and 0 means "zero fractional digits" (note that this applies to timestamp only, other datatypes have their own conventions). Now that we know where the gold is hidden: regression=# update pg_attribute set atttypmod = 0 where regression-# attrelid = (select oid from pg_class where relname = 'foo') regression-# and attnum = 1; UPDATE 1 regression=# \d foo Table "foo" Column | Type | Modifiers --------+-----------------------------+----------- f1 | timestamp(0) with time zone | f2 | timestamp(0) with time zone | This does not change the data already in the column, only cause a rounding adjustment to be applied during future inserts and updates. If you've already got fractional timestamps in the table, you could now fix 'em all with something like update foo set f1 = f1; BTW: if what you're unhappy about is not a readout from a table but just the result of "select now()", try "select current_timestamp(0)" instead. regards, tom lane