Thread: changing column size and type.
Hi, I am using pg 7.3.2. Is it possible to change: - column size ot existing table. - columnt size ( it is varchar basicaly). The problem is that the table have many freferences from and to it. many thanks, ivan.
Hi Ivan, I don't know if this is a recommended practice or not, but you can try the following. Use PgDump to back up the entire database as a text file. Edit the backup text file and change the CREATE TABLE statement for the table you want to change, changing the column width to the new width desired. Restore the database. The database will be restored with the new width for the column in the table. The ability to do this depends on the size of the database, whether it can be taken off line, etc. etc. HTH George ----- Original Message ----- From: "pginfo" <pginfo@t1.unisoftbg.com> To: <pgsql-sql@postgresql.org> Sent: Tuesday, April 15, 2003 9:21 AM Subject: [SQL] changing column size and type. > Hi, > > I am using pg 7.3.2. > > Is it possible to change: > - column size ot existing table. > - columnt size ( it is varchar basicaly). > > The problem is that the table have many freferences from and to it. > > many thanks, > ivan. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Hi George, It is possible to do it so, but I wanted some better solution. For example with oracle I can do it "online". regards, ivan. George Weaver wrote: > Hi Ivan, > > I don't know if this is a recommended practice or not, but you can try the > following. > > Use PgDump to back up the entire database as a text file. Edit the backup > text file and change the CREATE TABLE statement for the table you want to > change, changing the column width to the new width desired. Restore the > database. The database will be restored with the new width for the column > in the table. > > The ability to do this depends on the size of the database, whether it can > be taken off line, etc. etc. > > HTH > George > > ----- Original Message ----- > From: "pginfo" <pginfo@t1.unisoftbg.com> > To: <pgsql-sql@postgresql.org> > Sent: Tuesday, April 15, 2003 9:21 AM > Subject: [SQL] changing column size and type. > > > Hi, > > > > I am using pg 7.3.2. > > > > Is it possible to change: > > - column size ot existing table. > > - columnt size ( it is varchar basicaly). > > > > The problem is that the table have many freferences from and to it. > > > > many thanks, > > ivan. > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > >
Ivan,<br /><br /> This link show's how to do it online.<br /><br /><a class="moz-txt-link-freetext" href="http://www.edoceo.com/liberum/default.php?doc=postgresql-tricks">http://www.edoceo.com/liberum/default.php?doc=postgresql-tricks</a><br /><br/> There are other links around too.<br /><br /> Cheers<br /> Rudi<br /><br /><br /> pginfo wrote:<br /><blockquotecite="mid3E9CDF76.C2B2D50@t1.unisoftbg.com" type="cite"><pre wrap="">Hi George, It is possible to do it so, but I wanted some better solution. For example with oracle I can do it "online". regards, ivan. George Weaver wrote: </pre><blockquote type="cite"><pre wrap="">Hi Ivan, I don't know if this is a recommended practice or not, but you can try the following. Use PgDump to back up the entire database as a text file. Edit the backup text file and change the CREATE TABLE statement for the table you want to change, changing the column width to the new width desired. Restore the database. The database will be restored with the new width for the column in the table. The ability to do this depends on the size of the database, whether it can be taken off line, etc. etc. HTH George ----- Original Message ----- From: "pginfo" <a class="moz-txt-link-rfc2396E" href="mailto:pginfo@t1.unisoftbg.com"><pginfo@t1.unisoftbg.com></a> To: <a class="moz-txt-link-rfc2396E" href="mailto:pgsql-sql@postgresql.org"><pgsql-sql@postgresql.org></a> Sent: Tuesday, April 15, 2003 9:21 AM Subject: [SQL] changing column size and type. </pre><blockquote type="cite"><pre wrap="">Hi, I am using pg 7.3.2. Is it possible to change: - column size ot existing table. - columnt size ( it is varchar basicaly). The problem is that the table have many freferences from and to it. many thanks, ivan. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html</a> </pre></blockquote></blockquote><pre wrap=""> ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html</a> </pre></blockquote><br />
Hi Rudi, <p>I readet the doc, but it is about how to ann new column. <br />That is well documented in pgDoc's. <p>I willto change the column type of existing column. <br />For example if my columnt was declared as int4, I will to changeit to int8. <br />Or if it was varchar(20) I will to make it varchar(100) and so on. <br />Also I will it without todump the db. <p>regards, <br />ivan. <p>Rudi Starcevic wrote: <blockquote type="CITE"> Ivan, <p>This link show's how todo it online. <p><a class="moz-txt-link-freetext" href="http://www.edoceo.com/liberum/default.php?doc=postgresql-tricks">http://www.edoceo.com/liberum/default.php?doc=postgresql-tricks</a><p>There areother links around too. <p>Cheers <br />Rudi <br /> <p>pginfo wrote: <blockquote cite="mid3E9CDF76.C2B2D50@t1.unisoftbg.com"type="cite"><pre wrap="">Hi George, It is possible to do it so, but I wanted some better solution. For example with oracle I can do it "online". regards, ivan. George Weaver wrote: </pre><blockquote type="cite"><pre wrap="">Hi Ivan, I don't know if this is a recommended practice or not, but you can try the following. Use PgDump to back up the entire database as a text file. Edit the backup text file and change the CREATE TABLE statement for the table you want to change, changing the column width to the new width desired. Restore the database. The database will be restored with the new width for the column in the table. The ability to do this depends on the size of the database, whether it can be taken off line, etc. etc. HTH George ----- Original Message ----- From: "pginfo" <a class="moz-txt-link-rfc2396E" href="mailto:pginfo@t1.unisoftbg.com"><pginfo@t1.unisoftbg.com> </a>To: <a class="moz-txt-link-rfc2396E" href="mailto:pgsql-sql@postgresql.org"><pgsql-sql@postgresql.org> </a>Sent: Tuesday, April 15, 2003 9:21 AM Subject: [SQL] changing column size and type. </pre><blockquote type="cite"><pre wrap="">Hi, I am using pg 7.3.2. Is it possible to change: - column size ot existing table. - columnt size ( it is varchar basicaly). The problem is that the table have many freferences from and to it. many thanks, ivan. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html </a></pre></blockquote></blockquote><pre wrap=""> ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.postgresql.org/docs/faqs/FAQ.html </a></pre></blockquote> </blockquote>
Ivan, Oops sorry about that .. I should have read your email closer. Have you seen Joel Burton's doc at: http://techdocs.postgresql.org/techdocs/updatingcolumns.php Looks like you may need to use temporary tables and move the data back and forth. That's the way I do it. I'm pretty sure this also how it's done using the phpPgAdmin web admin tool. I guess since phpPgAdmin's a web tool the task is achieved 'online'. Cheers Best regards Rudi. > Hi Rudi, > > I readet the doc, but it is about how to ann new column. > That is well documented in pgDoc's. > > I will to change the column type of existing column. > For example if my columnt was declared as int4, I will to change it > to int8. Or if it was varchar(20) I will to make it varchar(100) and > so on. Also I will it without to dump the db. > > regards, > ivan. > > Rudi Starcevic wrote: > > > Ivan, > > > > This link show's how to do it online. > > > > http://www.edoceo.com/liberum/default.php?doc=postgresql-tricks > > > > There are other links around too. > > > > Cheers > > Rudi > > > > > > pginfo wrote: > > > >> Hi George, > >> > >> It is possible to do it so, but I wanted some better solution. > >> For example with oracle I can do it "online". > >> > >> regards, > >> ivan. > >> > >> George Weaver wrote: > >> > >> > >> > Hi Ivan, > >> > > >> > I don't know if this is a recommended practice or not, but you can > >> > try the > >> > following. > >> > > >> > Use PgDump to back up the entire database as a text file. Edit the > >> > backup > >> > text file and change the CREATE TABLE statement for the table you > >> > want to > >> > change, changing the column width to the new width desired. > >> > Restore the > >> > database. The database will be restored with the new width for the > >> > column > >> > in the table. > >> > > >> > The ability to do this depends on the size of the database, whether > >> > it can > >> > be taken off line, etc. etc. > >> > > >> > HTH > >> > George > >> > > >> > ----- Original Message ----- > >> > From: "pginfo" <pginfo@t1.unisoftbg.com> > >> > To: <pgsql-sql@postgresql.org> > >> > Sent: Tuesday, April 15, 2003 9:21 AM > >> > Subject: [SQL] changing column size and type. > >> > > >> > > >> >> Hi, > >> >> > >> >> I am using pg 7.3.2. > >> >> > >> >> Is it possible to change: > >> >> - column size ot existing table. > >> >> - columnt size ( it is varchar basicaly). > >> >> > >> >> The problem is that the table have many freferences from and to > >> >> it. > >> >> > >> >> many thanks, > >> >> ivan. > >> >> > >> >> > >> >> ---------------------------(end of > >> >> broadcast)--------------------------- > >> >> TIP 5: Have you checked our extensive FAQ? > >> >> > >> >> http://www.postgresql.org/docs/faqs/FAQ.html > >> >> > >> >> > >> > >> ---------------------------(end of > >> broadcast)--------------------------- > >> TIP 5: Have you checked our extensive FAQ? > >> > >> http://www.postgresql.org/docs/faqs/FAQ.html > >> > >> > >
Hi Rudi, I think it is good solution for now. Many thanks, ivan. Rudi Starcevic wrote: > Ivan, > > Oops sorry about that .. > I should have read your email closer. > Have you seen Joel Burton's doc at: > http://techdocs.postgresql.org/techdocs/updatingcolumns.php > > Looks like you may need to use temporary tables and move > the data back and forth. > > That's the way I do it. > I'm pretty sure this also how it's done using the phpPgAdmin > web admin tool. > I guess since phpPgAdmin's a web tool the task is achieved 'online'. > > Cheers > Best regards > Rudi. > > > Hi Rudi, > > > > I readet the doc, but it is about how to ann new column. > > That is well documented in pgDoc's. > > > > I will to change the column type of existing column. > > For example if my columnt was declared as int4, I will to change it > > to int8. Or if it was varchar(20) I will to make it varchar(100) and > > so on. Also I will it without to dump the db. > > > > regards, > > ivan. > > > > Rudi Starcevic wrote: > > > > > Ivan, > > > > > > This link show's how to do it online. > > > > > > http://www.edoceo.com/liberum/default.php?doc=postgresql-tricks > > > > > > There are other links around too. > > > > > > Cheers > > > Rudi > > > > > > > > > pginfo wrote: > > > > > >> Hi George, > > >> > > >> It is possible to do it so, but I wanted some better solution. > > >> For example with oracle I can do it "online". > > >> > > >> regards, > > >> ivan. > > >> > > >> George Weaver wrote: > > >> > > >> > > >> > Hi Ivan, > > >> > > > >> > I don't know if this is a recommended practice or not, but you can > > >> > try the > > >> > following. > > >> > > > >> > Use PgDump to back up the entire database as a text file. Edit the > > >> > backup > > >> > text file and change the CREATE TABLE statement for the table you > > >> > want to > > >> > change, changing the column width to the new width desired. > > >> > Restore the > > >> > database. The database will be restored with the new width for the > > >> > column > > >> > in the table. > > >> > > > >> > The ability to do this depends on the size of the database, whether > > >> > it can > > >> > be taken off line, etc. etc. > > >> > > > >> > HTH > > >> > George > > >> > > > >> > ----- Original Message ----- > > >> > From: "pginfo" <pginfo@t1.unisoftbg.com> > > >> > To: <pgsql-sql@postgresql.org> > > >> > Sent: Tuesday, April 15, 2003 9:21 AM > > >> > Subject: [SQL] changing column size and type. > > >> > > > >> > > > >> >> Hi, > > >> >> > > >> >> I am using pg 7.3.2. > > >> >> > > >> >> Is it possible to change: > > >> >> - column size ot existing table. > > >> >> - columnt size ( it is varchar basicaly). > > >> >> > > >> >> The problem is that the table have many freferences from and to > > >> >> it. > > >> >> > > >> >> many thanks, > > >> >> ivan. > > >> >> > > >> >> > > >> >> ---------------------------(end of > > >> >> broadcast)--------------------------- > > >> >> TIP 5: Have you checked our extensive FAQ? > > >> >> > > >> >> http://www.postgresql.org/docs/faqs/FAQ.html > > >> >> > > >> >> > > >> > > >> ---------------------------(end of > > >> broadcast)--------------------------- > > >> TIP 5: Have you checked our extensive FAQ? > > >> > > >> http://www.postgresql.org/docs/faqs/FAQ.html > > >> > > >> > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)