From tinarajraj@hotmail.com Wed Dec 5 13:06:21 2001 Received: from hotmail.com (dav16.law15.hotmail.com [64.4.22.120]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB5D6Lm73880 for ; Wed, 5 Dec 2001 08:06:21 -0500 (EST) (envelope-from tinarajraj@hotmail.com) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 5 Dec 2001 05:06:24 -0800 X-Originating-IP: [203.197.173.12] From: "tinar" To: Subject: how to chane the type Date: Wed, 5 Dec 2001 18:42:07 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0005_01C17DBC.8A4BED80" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 Message-ID: X-OriginalArrivalTime: 05 Dec 2001 13:06:24.0580 (UTC) FILETIME=[A470C840:01C17D8D] X-Archive-Number: 200112/73 X-Sequence-Number: 5896 This is a multi-part message in MIME format. ------=_NextPart_000_0005_01C17DBC.8A4BED80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable i have created a table userlist with fields username varchar(10) now i want to change it to username varchar(30) without dropping or recreating the table. how do i do it? tina ------=_NextPart_000_0005_01C17DBC.8A4BED80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
i have created a table
userlist with fields username=20 varchar(10)
now i want to change it to username=20 varchar(30)
without dropping or recreating the=20 table.
how do i do it?
tina
------=_NextPart_000_0005_01C17DBC.8A4BED80-- From sszabo@megazone23.bigpanda.com Thu Dec 6 16:31:04 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB6GV4m72401 for ; Thu, 6 Dec 2001 11:31:04 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 87D2FD5EC; Thu, 6 Dec 2001 08:31:09 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7D8575BED; Thu, 6 Dec 2001 08:31:09 -0800 (PST) Date: Thu, 6 Dec 2001 08:31:09 -0800 (PST) From: Stephan Szabo To: tinar Cc: Subject: Re: how to chane the type In-Reply-To: Message-ID: <20011206082604.Y25449-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200112/78 X-Sequence-Number: 5901 On Wed, 5 Dec 2001, tinar wrote: > i have created a table > userlist with fields username varchar(10) > now i want to change it to username varchar(30) > without dropping or recreating the table. > how do i do it? The best way is to recreate the table and rename them around. If you *REALLY* don't want to do that and have a recent backup (yes, I'm serious), you can muck with pg_attribute and change atttypmod for the attribute in question (from 14 to 34). From chriskl@familyhealth.com.au Fri Dec 7 01:45:39 2001 Received: from mail.iinet.net.au (symphony-05.iinet.net.au [203.59.3.37]) by postgresql.org (8.11.3/8.11.4) with SMTP id fB71jcm16895 for ; Thu, 6 Dec 2001 20:45:39 -0500 (EST) (envelope-from chriskl@familyhealth.com.au) Received: (qmail 6976 invoked by uid 666); 7 Dec 2001 01:45:36 -0000 Received: from unknown (HELO houston.familyhealth.com.au) (203.59.231.6) by mail.iinet.net.au with SMTP; 7 Dec 2001 01:45:36 -0000 Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id fB71jZI10709 for pgsql-sql@postgresql.org; Fri, 7 Dec 2001 09:45:35 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id fB71jVV10563; Fri, 7 Dec 2001 09:45:32 +0800 (WST) From: "Christopher Kings-Lynne" To: "Stephan Szabo" , "tinar" Cc: Subject: Re: how to chane the type Date: Fri, 7 Dec 2001 09:45:35 +0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 In-Reply-To: <20011206082604.Y25449-100000@megazone23.bigpanda.com> X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Archive-Number: 200112/87 X-Sequence-Number: 5910 What's the essential problem with changing column types in postgres? Is it similar to the DROP COLUMN problem? If the answer is that the table format only has allocated enough space per row for the existing type, then how is it possible that Stephen's hack below will not break things? Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo > Sent: Friday, 7 December 2001 12:31 AM > To: tinar > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] how to chane the type > > > > On Wed, 5 Dec 2001, tinar wrote: > > > i have created a table > > userlist with fields username varchar(10) > > now i want to change it to username varchar(30) > > without dropping or recreating the table. > > how do i do it? > > The best way is to recreate the table and rename > them around. If you *REALLY* don't want to do > that and have a recent backup (yes, I'm serious), > you can muck with pg_attribute and change > atttypmod for the attribute in question > (from 14 to 34). > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > From sszabo@megazone23.bigpanda.com Fri Dec 7 02:05:11 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB725Am20448 for ; Thu, 6 Dec 2001 21:05:11 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 5DCFBD5F1; Thu, 6 Dec 2001 18:05:14 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 537FA5BED; Thu, 6 Dec 2001 18:05:14 -0800 (PST) Date: Thu, 6 Dec 2001 18:05:14 -0800 (PST) From: Stephan Szabo To: Christopher Kings-Lynne Cc: tinar , Subject: Re: how to chane the type In-Reply-To: Message-ID: <20011206180253.J30180-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200112/88 X-Sequence-Number: 5911 On Fri, 7 Dec 2001, Christopher Kings-Lynne wrote: > What's the essential problem with changing column types in postgres? Is it > similar to the DROP COLUMN problem? > > If the answer is that the table format only has allocated enough space per > row for the existing type, then how is it possible that Stephen's hack below > will not break things? The hack below only works to change the max length of variable length attributes and only upward. I'd be very wary of trying to change the real type of a value except between ones that are bitwise compatible (like I think varchar and text are technically, but I'm not sure). > > The best way is to recreate the table and rename > > them around. If you *REALLY* don't want to do > > that and have a recent backup (yes, I'm serious), > > you can muck with pg_attribute and change > > atttypmod for the attribute in question > > (from 14 to 34).