Changed a column type from "integer" to varchar

[Ying Lu]

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

[Joshua D. Drake]

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.
[phone] - [e-mail] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Arthur Hoogervorst]

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 <yi*****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Greg Donald]

On Tue, 14 Sep 2004 13:33:32 -0700, Joshua D. Drake
<[e-mail]> 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/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Alvaro Herrera]

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
<[e-mail]> 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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ying Lu]

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
<[e-mail]> 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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Alvaro Herrera]

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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html