Postgres Pro
Downloads
Home   >   mailing lists

Thread: Alter field type?

Alter field type?

From
Bjørn T Johansen
Date:
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"
-----------------------------------------------------------------------------------------------

Re: Alter field type?

From
Tino Wildenhain
Date:
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

Re: Alter field type?

From
Greg Stark
Date:
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

Re: Alter field type?

From
Bjørn T Johansen
Date:
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"
-----------------------------------------------------------------------------------------------

Re: Alter field type?

From
Markus Bertheau
Date:
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>

upgrading minor versions

From
Jeff Amiel
Date:
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

Re: upgrading minor versions

From
Jan Wieck
Date:
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 #

Re: upgrading minor versions

From
Jeff Amiel
Date:
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
>
>
>

Re: upgrading minor versions

From
Doug McNaught
Date:
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

Re: upgrading minor versions

From
Jeff Amiel
Date:
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
>
>

Re: upgrading minor versions

From
Jan Wieck
Date:
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 #

Re: upgrading minor versions

From
Jeff Amiel
Date:
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