Changing column data type on an existing table

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