Thread: character type modification
Can I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3.
Thanks
Thanks
--
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
Existing table column data types cannot be altered in any stable version of postgres(7.4 or previous). I don't know if it is available in a beta release.
-----Original Message-----Can I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3.
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jodi Kanter
Sent: Tuesday, December 16, 2003 11:32 AM
To: Postgres Admin List
Subject: [ADMIN] character type modification
Thanks--
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
<!--[if !supportEmptyParas]--> <!--[endif]-->
<!--[if !supportEmptyParas]--> <!--[endif]-->
<!--[if !supportEmptyParas]--> <!--[endif]-->
On Tue, Dec 16, 2003 at 12:32:05PM -0500, Jodi Kanter wrote: > Can I alter a column from character varying(128) to text without having > to create a temp table? I am running 7.3.3. The FAQ has a question entitled "How do you remove a column from a table, or change its data type?": http://www.postgresql.org/docs/faqs/FAQ.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
This is another discuttion about this problem.
Please read these mails.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
I cannot write here all the syntax:
I'll give an example: you have a table called "person" and you have a column
called "name" varchar(10) and you want to change it into varchar(25).
alter person add column name_temp varchar(25);
update person set name_temp = name;
alter person drop column name;
alter person rename column name_temp to name;
This is it. Now depending on you database complexity if you have a complex
database and a lot of indexes or triggers, you will have to drop these items
and rebuild them.
Have fun.
Andy.
----- Original Message -----
From: "Chitta Ranjan Mishra" <c_r_mishra@yahoo.co.in>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Sent: Friday, December 12, 2003 5:46 AM
Subject: [ADMIN] Help---- Urgent
> Dear Sir,
> Thanks for your reply. Kindly send me the syntax
> for all this.
>
> Thanking you,
> Regds
> C.R.Mishra
>
>
>
> --- Andrei Bintintan <klodoma@ar-sd.net> wrote: >
> Create a new column
> >
> > alter <tablename> add column <newcolname>
> > varchar(25);
> >
> > Then copy the old column into the new column:
> > update <tablename> set <newcolname> = <oldcolname>;
> >
> > Drop the last column:
> > alter <tablename> drop column <oldcolname>;
> >
> > Rename the column:
> > alter <tablename> rename column <newcolname> to
> > <oldcolname>;
> >
> > Take care for INDEXES if there are any.
> >
> > This should do it.
> >
> > Best regards.
> >
> >
> > ----- Original Message -----
> > From: "Chitta Ranjan Mishra"
> > <c_r_mishra@yahoo.co.in>
> > To: <pgsql-admin@postgresql.org>
> > Sent: Thursday, December 11, 2003 7:19 AM
> > Subject: [ADMIN] Help---- Urgent
> >
> >
> > > Dear Sir,
> > > I wnat to alter the size of one of the column
> > of a
> > > table. How to do this in Postgres ? Plz help me.
> > > It's very urgent...
> > >
> > > I tried with the following syntax :
> > > but it failed....
> > >
> > > alter table tablename modify(coumnname
> > varchar(25));
> > >
> > > it's very urgnet...plz reply soon..
> > >
> > > Thanking you,
> > > Regds
> > > C.R.Mishra
> > >
> > >
> > >
> > >
> > >
> >
> ________________________________________________________________________
> > > Yahoo! India Mobile: Download the latest
> > polyphonic ringtones.
> > > Go to http://in.mobile.yahoo.com
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> ________________________________________________________________________
> Yahoo! India Mobile: Download the latest polyphonic ringtones.
> Go to http://in.mobile.yahoo.com
I'll give an example: you have a table called "person" and you have a column
called "name" varchar(10) and you want to change it into varchar(25).
alter person add column name_temp varchar(25);
update person set name_temp = name;
alter person drop column name;
alter person rename column name_temp to name;
This is it. Now depending on you database complexity if you have a complex
database and a lot of indexes or triggers, you will have to drop these items
and rebuild them.
Have fun.
Andy.
----- Original Message -----
From: "Chitta Ranjan Mishra" <c_r_mishra@yahoo.co.in>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Sent: Friday, December 12, 2003 5:46 AM
Subject: [ADMIN] Help---- Urgent
> Dear Sir,
> Thanks for your reply. Kindly send me the syntax
> for all this.
>
> Thanking you,
> Regds
> C.R.Mishra
>
>
>
> --- Andrei Bintintan <klodoma@ar-sd.net> wrote: >
> Create a new column
> >
> > alter <tablename> add column <newcolname>
> > varchar(25);
> >
> > Then copy the old column into the new column:
> > update <tablename> set <newcolname> = <oldcolname>;
> >
> > Drop the last column:
> > alter <tablename> drop column <oldcolname>;
> >
> > Rename the column:
> > alter <tablename> rename column <newcolname> to
> > <oldcolname>;
> >
> > Take care for INDEXES if there are any.
> >
> > This should do it.
> >
> > Best regards.
> >
> >
> > ----- Original Message -----
> > From: "Chitta Ranjan Mishra"
> > <c_r_mishra@yahoo.co.in>
> > To: <pgsql-admin@postgresql.org>
> > Sent: Thursday, December 11, 2003 7:19 AM
> > Subject: [ADMIN] Help---- Urgent
> >
> >
> > > Dear Sir,
> > > I wnat to alter the size of one of the column
> > of a
> > > table. How to do this in Postgres ? Plz help me.
> > > It's very urgent...
> > >
> > > I tried with the following syntax :
> > > but it failed....
> > >
> > > alter table tablename modify(coumnname
> > varchar(25));
> > >
> > > it's very urgnet...plz reply soon..
> > >
> > > Thanking you,
> > > Regds
> > > C.R.Mishra
> > >
> > >
> > >
> > >
> > >
> >
> ________________________________________________________________________
> > > Yahoo! India Mobile: Download the latest
> > polyphonic ringtones.
> > > Go to http://in.mobile.yahoo.com
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
> ________________________________________________________________________
> Yahoo! India Mobile: Download the latest polyphonic ringtones.
> Go to http://in.mobile.yahoo.com
----- Original Message -----From: Jodi KanterSent: Tuesday, December 16, 2003 7:32 PMSubject: [ADMIN] character type modificationCan I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3.
Thanks--
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
<!--[if !supportEmptyParas]--> <!--[endif]-->
<!--[if !supportEmptyParas]--> <!--[endif]-->
<!--[if !supportEmptyParas]--> <!--[endif]-->
Jodi Kanter <jkanter@virginia.edu> writes:
> Can I alter a column from character varying(128) to text without having
> to create a temp table? I am running 7.3.3.
Yeah, if you're brave enough: change the column's atttypid to 'text'
(25, I think, but check it) and atttypmod to -1 in pg_attribute.
This is sufficient since the two datatypes have the same representation
--- in general you can't just hack atttypid at random, but it will
work in this case.
Highly advisable to practice this maneuver in a scratch database.
BTW, if you're running 7.3.3 and not 7.3.4 (even better 7.3.5), you are
not being brave but foolish. 7.3.3's WAL-startup-failure bug will get
you sooner or later.
regards, tom lane