Thread: URGENT!!! changing Column size
Hi can we change the size of a column in postgres. I have a table named institution and column name is name varchar2(25), i want to change it to varchar2(50). Please let me know. --Mohan
Dnia 2003-10-27 18:10, Użytkownik mohan@physics.gmu.edu napisał: > Hi can we change the size of a column in postgres. I have a table named > institution and column name is name varchar2(25), i want to change it to > varchar2(50). Please let me know. alter table institution add column tmp varchar2(50); update institution set tmp=name; alter table institution drop column name; alter table institution rename tmp to name; (or something like this) Regards, Tomasz Myrta
On Monday 27 Oct 2003 5:10 pm, mohan@physics.gmu.edu wrote: > Hi can we change the size of a column in postgres. I have a table named > institution and column name is name varchar2(25), i want to change it to > varchar2(50). Please let me know. > > --Mohan try alter table institution add column newname varchar2(50); update institution set newname = name; alter table institution drop column namel; alter table institution rename column newname to name; > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
Dnia 2003-10-27 19:33, Użytkownik btober@seaworthysys.com napisał: > I've seen these sets of steps suggested in response to other such > inquires, but doesn't this break views on the associated table, or may > just not work because if a view dependency exists? It would be the second case (it won't work at all). You can use "cascade" when dropping column. After this you need to recreate views dropped together with a column. Regards, Tomasz Myrta
Hi, mohan@physics.gmu.edu wrote, On 10/27/2003 6:10 PM: > Hi can we change the size of a column in postgres. I have a table named > institution and column name is name varchar2(25), i want to change it to > varchar2(50). Please let me know. 1 solution: begin; create temporary table temp as select * from mytable; drop table mytable; create table mytable (name varchar(50)); insert into mytable select CAST(name AS varchar(50)) from temp; drop table temp; commit; C.
why not just pg_dump dbname > olddb.out pico olddb.out edit the section that defines the table save and exit dropdb dbname createdb dbname psql dbname < olddb.out no fuss no muss... Ted --- mohan@physics.gmu.edu wrote: > Hi can we change the size of a column in postgres. I > have a table named > institution and column name is name varchar2(25), i > want to change it to > varchar2(50). Please let me know. > > --Mohan > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/
Dopping the whole database just for a column change? <br /><br /> On Tue, 2003-10-28 at 10:00, Theodore Petrosky wrote:<blockquote type="CITE"><pre><font color="#737373"><i>why not just pg_dump dbname > olddb.out pico olddb.out edit the section that defines the table save and exit dropdb dbname createdb dbname psql dbname < olddb.out no fuss no muss... Ted --- mohan@physics.gmu.edu wrote: > Hi can we change the size of a column in postgres. I > have a table named > institution and column name is name varchar2(25), i > want to change it to > varchar2(50). Please let me know. > > --Mohan > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________ Do you Yahoo!? Exclusive Video Premiere - Britney Spears</i></font> <a href="http://launch.yahoo.com/promos/britneyspears/"><u>http://launch.yahoo.com/promos/britneyspears/</u></a> <font color="#737373"> ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? </font><a href="http://www.postgresql.org/docs/faqs/FAQ.html"><u>http://www.postgresql.org/docs/faqs/FAQ.html</u></a> <font color="#737373"></font></pre></blockquote>
On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote:
> Dopping the whole database just for a column change?
I guess some people have really small databases that don't take 3 days to dump
and reload. :-)
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
> > On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote: > > Dopping the whole database just for a column change? > > I guess some people have really small databases that don't take 3 days to dump > and reload. :-) > And you are on the safe side regarding indexes, views, procedures, ... Regards, Christoph
I have a php script that patches database, comparing pg_catalog's tables to input files. One thing it can do, but I can't take responsibility ;) is changing the type of a column. It's basically the same that everyone wrote, except that I also examine dependencies, as broad an examination as I could think about. However; the code is not yet ready, and not my exclusive possession, but the steps I could tell you are: 1. Note oid: SELECT oid, relname from pg_class WHERE relname='institution' 2. Identify the attribute: SELECT * from pg_attribute WHERE attrelid = xxx AND ... 3. Search for depends: SELECT d.* FROM pg_depend d, pg_attribute a WHERE refobjid=a.attrelid and refobjsubid=a.attnum AND ... -- you are on your own here ;) 4. Drop those dependencies (more likely, foreign keys, constraints, indexes) 5. Do the change 6. Apply dependencies. HTH, G. ------------------------------- cut here ------------------------------- ----- Original Message ----- From: <mohan@physics.gmu.edu> Sent: Monday, October 27, 2003 6:10 PM > Hi can we change the size of a column in postgres. I have a table named > institution and column name is name varchar2(25), i want to change it to > varchar2(50). Please let me know. > > --Mohan