Обсуждение: type of a field
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
Hi 1 u will have to use alter table to change the tables name RTFM 2 then create the new table with the changed columns 3 then insert into <newtable> values (select oldcolumn as newcolumn, ect... from <renamedtable>); but i did not read u'r question correctly. i would do steps 1 and 2 above and then write a function in php that select * from <renamed table> and insert into <newtable> -----Original Message----- From: ameen eetemadi [mailto:ameen78101@yahoo.com] Sent: Tuesday, May 07, 2002 3:01 PM To: pgsql-php@postgresql.org Subject: [PHP] type of a field 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 >
You can't yet... at least not with an ALTER statement. The only way to currenty do this is by dumping the table (through pg_dump), altering the type of the field, and then re-ipmorting the table. Rumor has it that they are working on an ALTER statement to accomplish this for 7.3 or 7.4. -Dan Quoting ameen eetemadi <ameen78101@yahoo.com>: > I want to change the type of a field in postgres . > How can I do it ?
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"
> You can't yet... at least not with an ALTER statement. The only way to > currenty do this is by dumping the table (through pg_dump), > altering the type > of the field, and then re-ipmorting the table. > > Rumor has it that they are working on an ALTER statement to > accomplish this > for 7.3 or 7.4. Development currently stalled ;) Don't bet on it being there... Chris