470,602 Members | 1,504 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,602 developers. It's quick & easy.

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,

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

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

Nov 23 '05 #1
6 12286
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
---------------------------(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

Nov 23 '05 #2
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

Nov 23 '05 #3
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/

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

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

Nov 23 '05 #4
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)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #5
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?

---------------------------(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

Nov 23 '05 #6
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

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Vijay | last post: by
20 posts views Thread by chutsu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.