Обсуждение: Alter field type?
Is it possible to alter field type (from varchar to text) without making a new table? Regards, BTJ -- ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
Hi, Am Do, den 26.08.2004 schrieb Bjørn T Johansen um 9:48: > Is it possible to alter field type (from varchar to text) without making > a new table? In 8.0 I think. Common practice in 7.4.x is: BEGIN work; ALTER TABLE yourtable ADD COLUMN tempcolumn text; UPDATE yourtable SET tempcolumn = origcolumn; ALTER TABLE yourtable DROP COLUMN origcolumn; ALTER TABLE yourtable RENAME tempcolumn TO origcolumn; COMMIT work; Since text and (unlimited) varchar are quite the same, you could get away with modifying the system tables. But I would not recomment to do so. Regards Tino Wildenhain
Bjørn T Johansen <btj@havleik.no> writes: > Is it possible to alter field type (from varchar to text) without making a new > table? You can add a new column, update the table to set the new column to the old value. then drop the old column. Then you might want to vacuum full the table. In 8.0 there will be a command to do what you want, but it will still have to do all the same work as I described above. -- greg
That looks like a recipe, thx... :) BTJ Tino Wildenhain wrote: > Hi, > > Am Do, den 26.08.2004 schrieb Bjørn T Johansen um 9:48: > >>Is it possible to alter field type (from varchar to text) without making >>a new table? > > > In 8.0 I think. > Common practice in 7.4.x is: > > BEGIN work; > ALTER TABLE yourtable ADD COLUMN tempcolumn text; > UPDATE yourtable SET tempcolumn = origcolumn; > ALTER TABLE yourtable DROP COLUMN origcolumn; > ALTER TABLE yourtable RENAME tempcolumn TO origcolumn; > COMMIT work; > > Since text and (unlimited) varchar are quite the same, > you could get away with modifying the system tables. > But I would not recomment to do so. > > Regards > Tino Wildenhain > -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen (BSc,MNIF) Executive Manager btj@havleik.no Havleik Consulting Phone : +47 21 69 15 20 Bjørnebærstien 57 Fax : +47 41 13 09 15 N-1348 Rykkinn Cellular : +47 926 93 298 http://www.havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
On 26 Aug 2004 04:36:18 -0400, Greg Stark <gsstark@mit.edu> wrote: > > Bjørn T Johansen <btj@havleik.no> writes: > > > Is it possible to alter field type (from varchar to text) without making a new > > table? > > You can add a new column, update the table to set the new column to the old > value. then drop the old column. Then you might want to vacuum full the table. Does vacuum full completely remove the dropped column? Thanks -- Markus Bertheau <mbertheau@gmail.com>
This may sound like a silly question.... I am currently running 7.4.2. I would like to upgrade to 7.4.5... So I downloaded postgresql-7.4.5.tar.gz <ftp://ftp21.us.postgresql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz> uncompressed... configure... make make install I run psql and it gives me the" Welcome to psql 7.4.5, the PostgreSQL interactive terminal." So far so good...it says 7.4.5. but when I select version() from the database that I connected to with psql, I still receive: PostgreSQL 7.4.2 on i386-portbld-freebsd5.2.1, compiled by GCC cc (GCC) 3.3.3 [FreeBSD] 20031106 Is the database itself versioned? Do I need to do a dump/restore? Jeff
On 8/27/2004 12:21 PM, Jeff Amiel wrote: > This may sound like a silly question.... > I am currently running 7.4.2. > I would like to upgrade to 7.4.5... > > So I downloaded postgresql-7.4.5.tar.gz > <ftp://ftp21.us.postgresql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz> > uncompressed... > configure... try eval ./configure `/usr/bin/pg_config --configure` Jan > make > make install > > I run psql and it gives me the" Welcome to psql 7.4.5, the PostgreSQL > interactive terminal." > So far so good...it says 7.4.5. > > but when I select version() from the database that I connected to with > psql, I still receive: > PostgreSQL 7.4.2 on i386-portbld-freebsd5.2.1, compiled by GCC cc (GCC) > 3.3.3 [FreeBSD] 20031106 > > Is the database itself versioned? > > Do I need to do a dump/restore? > > Jeff > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Ok......still no dice. still shows 7.4.2 when I select version()... I must surely be doing something silly here... Jan Wieck wrote: > On 8/27/2004 12:21 PM, Jeff Amiel wrote: > >> This may sound like a silly question.... >> I am currently running 7.4.2. >> I would like to upgrade to 7.4.5... >> >> So I downloaded postgresql-7.4.5.tar.gz >> <ftp://ftp21.us.postgresql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz> >> >> uncompressed... >> configure... > > > try > > eval ./configure `/usr/bin/pg_config --configure` > > > Jan > >> make >> make install >> >> I run psql and it gives me the" Welcome to psql 7.4.5, the PostgreSQL >> interactive terminal." >> So far so good...it says 7.4.5. >> >> but when I select version() from the database that I connected to >> with psql, I still receive: >> PostgreSQL 7.4.2 on i386-portbld-freebsd5.2.1, compiled by GCC cc >> (GCC) 3.3.3 [FreeBSD] 20031106 >> >> Is the database itself versioned? >> >> Do I need to do a dump/restore? >> >> Jeff >> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings > > >
Jeff Amiel <jamiel@istreamimaging.com> writes: > So I downloaded postgresql-7.4.5.tar.gz > <ftp://ftp21.us.postgresql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz> > uncompressed... > configure... > make > make install > > I run psql and it gives me the" Welcome to psql 7.4.5, the PostgreSQL > interactive terminal." > So far so good...it says 7.4.5. > > but when I select version() from the database that I connected to with > psql, I still receive: > PostgreSQL 7.4.2 on i386-portbld-freebsd5.2.1, compiled by GCC cc > (GCC) 3.3.3 [FreeBSD] 20031106 Did you shut down and restart the server after the upgrade? Are you sure the new binaries went where you think they did? -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Duh. I never restarted the server. sheesh. thanks to all. Jeff Doug McNaught wrote: >Did you shut down and restart the server after the upgrade? Are you >sure the new binaries went where you think they did? > >-Doug > >
On 8/27/2004 12:44 PM, Jeff Amiel wrote: > Ok......still no dice. > still shows 7.4.2 when I select version()... > I must surely be doing something silly here... You try to install 7.4.5 from souces over the FreeBSD Port build. The problem is that the options to configure need to be the same. You can see the options the port maintainer used by running the original pg_config with --configure option. What you have done so far and what you should undo is that you installed a whole bunch of stuff in /usr/local/pgsql, which is an unusual location for FreeBSD. Then you run the configure for 7.4.5 with exactly the same options, do a "gmake clean all" and as root "gmake install install-all-headers". Then you restart the postmaster and are in business. Jan > > Jan Wieck wrote: > >> On 8/27/2004 12:21 PM, Jeff Amiel wrote: >> >>> This may sound like a silly question.... >>> I am currently running 7.4.2. >>> I would like to upgrade to 7.4.5... >>> >>> So I downloaded postgresql-7.4.5.tar.gz >>> <ftp://ftp21.us.postgresql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz> >>> >>> uncompressed... >>> configure... >> >> >> try >> >> eval ./configure `/usr/bin/pg_config --configure` >> >> >> Jan >> >>> make >>> make install >>> >>> I run psql and it gives me the" Welcome to psql 7.4.5, the PostgreSQL >>> interactive terminal." >>> So far so good...it says 7.4.5. >>> >>> but when I select version() from the database that I connected to >>> with psql, I still receive: >>> PostgreSQL 7.4.2 on i386-portbld-freebsd5.2.1, compiled by GCC cc >>> (GCC) 3.3.3 [FreeBSD] 20031106 >>> >>> Is the database itself versioned? >>> >>> Do I need to do a dump/restore? >>> >>> Jeff >>> >>> >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 7: don't forget to increase your free space map settings >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
thanks for the heads up about the pg_config stuff... I wondered about the difference.....I have several boxes that were installed via ports (which have the binaries reside in /usr/local/pgsql) and some hand installed from the tar (which are in usr/local/bin). At least that explains why I have binaries in 2 different places!! Thanks again....(my problem was that I needed to restart the postmaster....) Jeff Jan Wieck wrote: > You try to install 7.4.5 from souces over the FreeBSD Port build. The > problem is that the options to configure need to be the same. You can > see the options the port maintainer used by running the original > pg_config with --configure option. > > What you have done so far and what you should undo is that you > installed a whole bunch of stuff in /usr/local/pgsql, which is an > unusual location for FreeBSD. > > Then you run the configure for 7.4.5 with exactly the same options, do > a "gmake clean all" and as root "gmake install install-all-headers". > Then you restart the postmaster and are in business. > > > Jan