pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: Matteo Beccati <m.beccati@crpsoftware.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Timestamp output
Date: Tue, 26 Feb 2002 10:55:41 -0500
Message-ID: <29989.1014738941@sss.pgh.pa.us> (raw)
In-Reply-To: <20020226223623.L56253-100000@houston.familyhealth.com.au>
References: <20020226223623.L56253-100000@houston.familyhealth.com.au>

Christopher Kings-Lynne <chriskl@familyhealth.com.au> 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



view thread (9+ messages)  latest in thread

Message-ID: <29989.1014738941@sss.pgh.pa.us>
Permalink:  ../29989.1014738941@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29989.1014738941@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au, m.beccati@crpsoftware.it
  Subject: Re: Timestamp output
  In-Reply-To: <29989.1014738941@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox