Postgres Pro
Downloads
Home   >   mailing lists

Thread: Changing column data type on an existing table

Changing column data type on an existing table

From
Joe Audette
Date:
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

Re: Changing column data type on an existing table

From
Brad Nicholson
Date:
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.

Re: Changing column data type on an existing table

From
Bricklen Anderson
Date:
> 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.
_______________________________

Re: Changing column data type on an existing table

From
David Fetter
Date:
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!

Re: Changing column data type on an existing table

From
Richard Huxton
Date:
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