From ssakkaravel@ivesia.com Tue Sep  2 09:50:51 2003
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 37A7ED1B94F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Sep 2003 09:50:51 +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 44525-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Sep 2003 06:50:21 -0300 (ADT)
Received: from chn-dns.primus-india.net (unknown [203.196.153.7])
	by svr1.postgresql.org (Postfix) with ESMTP id 699CFD1B8F5
	for <pgsql-sql@postgresql.org>; Tue,  2 Sep 2003 06:50:17 -0300 (ADT)
Received: from IVESIA67 (ptil-138-190-chn.primus-india.net [203.196.190.138]
	(may be forged))
	by chn-dns.primus-india.net (8.12.8/8.12.8) with SMTP id h829QOnm014642
	for <pgsql-sql@postgresql.org>; Tue, 2 Sep 2003 14:56:27 +0530
Message-ID: <000c01c37137$a6e87ce0$7502a8c0@hdsc.com>
From: "Senthil Kumar S" <ssakkaravel@ivesia.com>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Change column data type
Date: Tue, 2 Sep 2003 15:20:29 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0007_01C37165.BE623A90"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_50_60
X-Spam-Level: 
X-Archive-Number: 200309/17
X-Sequence-Number: 14995

This is a multi-part message in MIME format.

------=_NextPart_000_0007_01C37165.BE623A90
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Friends,

Using Postgres 7.3.4 over the linux server 7.3.=20

Is it possible to alter/change the data type of a existing table's column, =
with out dropping and recreating a column of same name.

Thanks for ur knowledge sharing.


Regards
Kumar


------=_NextPart_000_0007_01C37165.BE623A90
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Using Postgres 7.3.4 over the linux server 7.3. </FONT>=
</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Is it possible to alter/change the data type of a exist=
ing=20
table's column, with out dropping and recreating a column of same=20
name.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thanks for ur knowledge sharing.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Regards</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<P></P></BODY></HTML>

------=_NextPart_000_0007_01C37165.BE623A90--




From sgnerd@yahoo.com.sg Tue Sep  2 10:57:41 2003
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 34C5BD1B8BD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Sep 2003 10:57:41 +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 48980-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Sep 2003 07:57:12 -0300 (ADT)
Received: from smtp011.mail.yahoo.com (smtp011.mail.yahoo.com
 [216.136.173.31])
	by svr1.postgresql.org (Postfix) with SMTP id B9335D1B956
	for <pgsql-sql@postgresql.org>; Tue,  2 Sep 2003 07:57:08 -0300 (ADT)
Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67)
	(sgnerd@203.196.190.138 with login)
	by smtp.mail.vip.sc5.yahoo.com with SMTP; 2 Sep 2003 10:57:09 -0000
Message-ID: <002a01c37140$fe6edd70$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Fw: Change column data type
Date: Tue, 2 Sep 2003 16:27:13 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0027_01C3716F.1110AD40"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_50_60
X-Spam-Level: 
X-Archive-Number: 200309/7
X-Sequence-Number: 14985

This is a multi-part message in MIME format.

------=_NextPart_000_0027_01C3716F.1110AD40
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Dear Friends,

Using Postgres 7.3.4 over the linux server 7.3.=20

Is it possible to alter/change the data type of a existing table's column, =
with out dropping and recreating a column of same name.

Thanks for ur knowledge sharing.


Regards
Kumar




------=_NextPart_000_0027_01C3716F.1110AD40
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV style=3D"FONT: 10pt arial"><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Using Postgres 7.3.4 over the linux server 7.3. </FONT>=
</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Is it possible to alter/change the data type of a exist=
ing=20
table's column, with out dropping and recreating a column of same=20
name.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Thanks for ur knowledge sharing.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Regards</FONT></DIV>
<DIV><FONT size=3D2>Kumar</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<P></P>
<P></P>
<P></P></BODY></HTML>

------=_NextPart_000_0027_01C3716F.1110AD40--




From scott.marlowe@ihs.com Thu Sep  4 15:19:54 2003
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 B9081D1B899
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  4 Sep 2003 15:19:54 +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 47409-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  4 Sep 2003 12:19:44 -0300 (ADT)
Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222])
	by svr1.postgresql.org (Postfix) with ESMTP id A9533D1B89A
	for <pgsql-sql@postgresql.org>; Thu,  4 Sep 2003 12:19:43 -0300 (ADT)
Received: from css120.ihs.com (css120.ihs.com [170.207.105.120])
	by mail1.ihs.com (8.12.9/8.12.9) with ESMTP id h84FHup5003000;
	Thu, 4 Sep 2003 09:17:56 -0600 (MDT)
Date: Thu, 4 Sep 2003 09:16:01 -0600 (MDT)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Fw: Change column data type
In-Reply-To: <002a01c37140$fe6edd70$7502a8c0@hdsc.com>
Message-ID: <Pine.LNX.4.33.0309040912510.27003-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-IHS-MailScanner: Found to be clean
X-IHS-MailScanner-SpamCheck: 
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/16
X-Sequence-Number: 14994

On Tue, 2 Sep 2003, Kumar wrote:

> 
> Dear Friends,
> 
> Using Postgres 7.3.4 over the linux server 7.3. 
> 
> Is it possible to alter/change the data type of a existing table's 
> column, with out dropping and recreating a column of same name.

Only for certain types, and only by hacking the system catalogs, which is 
a procedure fraught with danger.  i.e. backup all your data, then proceed 
with caution.

Generally, it's best to create a new column and put the data in there and 
drop the old column.  Note you can do this in a transaction, so you can 
roll it back should you realize you've made some kind of error.

begin;
alter table t1 add column c1 int8;
update t1 set c1=c2;
alter table t1 drop column c2;
commit;
vacuum;




From dev@archonet.com Fri Sep  5 08:15:53 2003
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 0FD98D1B89D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  5 Sep 2003 08:15:53 +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 10967-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  5 Sep 2003 05:15:22 -0300 (ADT)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by svr1.postgresql.org (Postfix) with ESMTP id F0F36D1B89F
	for <pgsql-sql@postgresql.org>; Fri,  5 Sep 2003 05:15:19 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1)
	id 19vBkW-0007u5-0Z; Fri, 05 Sep 2003 09:15:21 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id E42011686E; Fri,  5 Sep 2003 09:15:19 +0100 (BST)
Received: from client17.archonet.com (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 7AFB71686B; Fri,  5 Sep 2003 09:15:18 +0100 (BST)
From: Richard Huxton <dev@archonet.com>
To: "Senthil Kumar S" <ssakkaravel@ivesia.com>,
	"psql" <pgsql-sql@postgresql.org>
Subject: Re: Change column data type
Date: Fri, 5 Sep 2003 09:15:18 +0100
User-Agent: KMail/1.5
References: <000c01c37137$a6e87ce0$7502a8c0@hdsc.com>
In-Reply-To: <000c01c37137$a6e87ce0$7502a8c0@hdsc.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200309050915.18531.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/25
X-Sequence-Number: 15003

On Tuesday 02 September 2003 10:50, Senthil Kumar S wrote:
> Dear Friends,
>
> Using Postgres 7.3.4 over the linux server 7.3.
>
> Is it possible to alter/change the data type of a existing table's column,
> with out dropping and recreating a column of same name.
>
> Thanks for ur knowledge sharing.

If you mean changing a varchar to a date or int4 to text, then no. If you want 
to change the size of a varchar field, people seem to have done that in the 
past (check the archives for details).

Otherwise, you'll have to wrap the drop/recreate in a transaction along with 
whatever supporting updates you need.
-- 
  Richard Huxton
  Archonet Ltd