469,903 Members | 1,944 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

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"
-----------------------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
11 1836
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

Bjørn T Johansen <bt*@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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3
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
bt*@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"
-----------------------------------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4
On 26 Aug 2004 04:36:18 -0400, Greg Stark <gs*****@mit.edu> wrote:

Bjørn T Johansen <bt*@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 <mb*******@gmail.com>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6
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. #
#================================================= = Ja******@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #8
Jeff Amiel <ja****@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #9
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #10
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 ma*******@postgresql.org)

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #11
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Geoff Jones | last post: by
2 posts views Thread by leegold2 | last post: by
3 posts views Thread by sparks | last post: by
2 posts views Thread by goyosito | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.