Alter field type?

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:

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:

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:

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 #