Re: 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(at)postgresql(dot)org)
>
>

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.