Thread: Changed a column type from "integer" to varchar

Hi,

I have a question about alter a column's type in a postgreSQL table.

For example, I have 10, 000 records in a table name "test", I'd like to
change column "machineID" type from integer to varchar. I am looking for
something like:

alter table test alter column machineID ... ... varchar

Thanks in advance,

Hello,

You can not currently change the data type with alter table.

J

Ying Lu wrote:
> Hi,
>
> I have a question about alter a column's type in a postgreSQL table.
>
> For example, I have 10, 000 records in a table name "test", I'd like to
> change column "machineID" type from integer to varchar. I am looking for
> something like:
>
> alter table test alter column machineID ... ... varchar
>
> Thanks in advance,
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Hi,

If you're using 7.4 or below (I'm not sure if 7.5 is able to do this),
you'll end up writing the data first to a temporary table, as in (for
example):


SELECT *  INTO TEMPORARY MyTable
FROM yourtable;

DROP TABLE yourtable;

CREATE TABLE yourtable (
  /* with varchar stuff */
) WITH OIDS;

INSERT into yourtable
(
  your field list
)
SELECT
  cast(anumber as varchar(20)),
  etc,
from MyTable;

Drop MyTable;





Regards,

Arthur


On Tue, 14 Sep 2004 16:14:33 -0400, Ying Lu <ying_lu@cs.concordia.ca> wrote:
> Hi,
>
> I have a question about alter a column's type in a postgreSQL table.
>
> For example, I have 10, 000 records in a table name "test", I'd like to
> change column "machineID" type from integer to varchar. I am looking for
> something like:
>
> alter table test alter column machineID ... ... varchar
>
> Thanks in advance,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

On Tue, 14 Sep 2004 13:33:32 -0700, Joshua D. Drake
<jd@commandprompt.com> wrote:
> You can not currently change the data type with alter table.

Are there any plans to add this functionality?

What's the best workaround?  Add a new column, copy data from old
column to new column, drop old column?


--
Greg Donald
http://destiney.com/

On Tue, Sep 14, 2004 at 03:53:07PM -0500, Greg Donald wrote:
> On Tue, 14 Sep 2004 13:33:32 -0700, Joshua D. Drake
> <jd@commandprompt.com> wrote:
> > You can not currently change the data type with alter table.
>
> Are there any plans to add this functionality?

It's in 8.0 already.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No necesitamos banderas
No reconocemos fronteras"                  (Jorge González)

Currently, what I did is like

. alter table test add column machineIDnew varchar;
. update test set machineIDnew = machineID;
. alter table test rename machineIDnew to machineID;
. vacuum full table;

If better ways, please let me know.

Thanks a lot,


Greg Donald wrote:

>On Tue, 14 Sep 2004 13:33:32 -0700, Joshua D. Drake
><jd@commandprompt.com> wrote:
>
>
>>You can not currently change the data type with alter table.
>>
>>
>
>Are there any plans to add this functionality?
>
>What's the best workaround?  Add a new column, copy data from old
>column to new column, drop old column?
>
>
>
>

On Tue, Sep 14, 2004 at 01:33:32PM -0700, Joshua D. Drake wrote:

> You can not currently change the data type with alter table.

... but you can add a new column with the desired type, UPDATE it with
the transformed data, and the DROP the old column.

> Ying Lu wrote:

> >I have a question about alter a column's type in a postgreSQL table.
> >
> >For example, I have 10, 000 records in a table name "test", I'd like to
> >change column "machineID" type from integer to varchar. I am looking for
> >something like:
> >
> >alter table test alter column machineID ... ... varchar

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)