Обсуждение: Changing column data type on an existing table
Hi, I have an app that I released with a particular field as varchar 255. Can someone give me a script example I can use to make an upgrade script to change it to text or at least to larger varchar without losing existing data? I support 3 different dbs in my app, Postgre is the newest and least familiar to me but I am trying to learn. Any help much appreciated. Joe Audette joe_audette [at] yahoo dotcom http://www.joeaudette.com http://www.mojoportal.com
Something like BEGIN; LOCK table_name; ALTER TABLE table_name RENAME col_a to col_a_old; ALTER TABLE table_name ADD COLUMN col_a text; UPDATE table_name SET col_a=col_a_old; ALTER TABLE table_name DROP COLUMN col_a_old; COMMIT; If you have any referential integrity on the column, you'll have to mess with that first. Joe Audette wrote: >Hi, > >I have an app that I released with a particular field >as varchar 255. > >Can someone give me a script example I can use to make >an upgrade script to change it to text or at least to >larger varchar without losing existing data? > >I support 3 different dbs in my app, Postgre is the >newest and least familiar to me but I am trying to >learn. > >Any help much appreciated. > >Joe Audette > >joe_audette [at] yahoo dotcom >http://www.joeaudette.com >http://www.mojoportal.com > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
> Joe Audette wrote: > > Hi, > > I have an app that I released with a particular field > as varchar 255. > > Can someone give me a script example I can use to make > an upgrade script to change it to text or at least to > larger varchar without losing existing data? > > I support 3 different dbs in my app, Postgre is the > newest and least familiar to me but I am trying to > learn. > > Any help much appreciated. > > Joe Audette In v8 at least, you can issue: alter TABLE <tablename> ALTER <column_name> TYPE text; -- _______________________________ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. _______________________________
On Fri, May 13, 2005 at 10:34:34AM -0700, Joe Audette wrote: > Hi, > > I have an app that I released with a particular field > as varchar 255. If you're using PostgreSQL 8, there is an option to ALTER TABLE that does this. The docs on ALTER TABLE including man alter_table have examples :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
Joe Audette wrote: > Hi, > > I have an app that I released with a particular field > as varchar 255. > > Can someone give me a script example I can use to make > an upgrade script to change it to text or at least to > larger varchar without losing existing data? Others have answered for version 8. If you are running one of the 7.x releases, you should google for "pg_attribute atttypmod" and see how you can change that value to extend varchars. HTH -- Richard Huxton Archonet Ltd