X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id ECD92D1B561
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 15:18:18 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 47139-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 12:16:38 -0300 (ADT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id 041B6D1B550
	for <pgsql-sql@postgresql.org>; Thu, 14 Aug 2003 12:18:05 -0300 (ADT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Thu Aug 14 17:18:04 2003 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id RAA27325; Thu, 14 Aug 2003 17:18:03 +0200 (METDST)
Message-ID: <3F3BA82A.96D2F310@rodos.fzk.de>
Date: Thu, 14 Aug 2003 17:18:03 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, yudie@axiontech.com
Subject: Re: Changing data type must recreate all views?
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message
	header 'Received' Received: \234by rodos.fzk.de...            ^
X-Archive-Number: 200308/175
X-Sequence-Number: 14791

>
> I need to change column data type from integer to float8
> That mean to droping table and recreate a new one and can lost the
original
> object id.
> Do i need to recreate all views and triggers that relate to that
table?
> if that so, is there anyway to do that without touching views and
triggers?
>
No, you don't have to drop and re-create.
How about
ALTER TABLE yourTable ADD COLUMN yourNewColumn double precision ;
UPDATE yourTable SET yourNewColumn = yourOldColumn ;
ALTER TABLE yourTable DROP COLUMN yourOldColumn ;
ALTER TABLE yourTable RENAME COLUMN yourNewColumn TO yourOldColumn ;

Views and triggers should work AFAIK, but if there are procedures
referencing the data type
of yourOldColumn, you have to re-create them changing the parameter type
from
integer to double precision.

Regards, Christoph