I want to change the type of a field in postgres .
How can I do it ?
__________________________________________________
Do You Yahoo!?
Yahoo! Health - your guide to health and wellness
http://health.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Short answer: You can't
Long answer: There are many workarounds.
Workaround 1:
dump the table with pg_dump dbase -t tablename
edit dump file to change table definition
drop and reload table.
Workaround 2:
Let's say you wanna change a varchar to a text type.
Original table t1:
col | type
----------
nam | varchar(32)
id | int4
select nam::text, id into t2 from t1;
drop table t1;
alter table t2 rename to t1;
You can do the above in a transaction if you are on a live database. I'd
back up the data first just to be sure.
On Tue, 7 May 2002, ameen eetemadi wrote:
> I want to change the type of a field in postgres .
> How can I do it ?
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - your guide to health and wellness
> http://health.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
on 5/7/02 7:00 AM, ameen78101@yahoo.com purportedly said:
> I want to change the type of a field in postgres .
> How can I do it ?
You can't, at least not exactly. I believe there is some info in either the
FAQ, documentation, or non-FAQ documentation on the Postgres site.
In essence, your choices are:
1) recreate the field: rename the current field, then create a new field
with the same name and desired options; or
2) recreate the table: rename the current table, create a new table with the
proper name and field definitions as desired, drop the old table. You can
use SELECT INTO to transfer data from the old table to new.
In both cases, however, you can run into problems with triggers and other
server-side functions that use the field or table. I believe there are ways
around this, but I don't recall them at the moment.
Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly