Alter field type?

[Bjørn T Johansen]

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

[Tino Wildenhain]

Re: Alter field type?

Hi,

Am Do, den 26.08.2004 schrieb Bjørn T Johansen um 9:48:[color=blue]
> Is it possible to alter field type (from varchar to text) without making
> a new table?[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Greg Stark]

Re: Alter field type?


Bjørn T Johansen <btj@havleik.no > writes:
[color=blue]
> Is it possible to alter field type (from varchar to text) without making a new
> table?[/color]

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[Bjørn T Johansen]

Re: Alter field type?

That looks like a recipe, thx... :)

BTJ

Tino Wildenhain wrote:[color=blue]
> Hi,
>
> Am Do, den 26.08.2004 schrieb Bjørn T Johansen um 9:48:
>[color=green]
>>Is it possible to alter field type (from varchar to text) without making
>>a new table?[/color]
>
>
> 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
>[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Markus Bertheau]

Re: Alter field type?

On 26 Aug 2004 04:36:18 -0400, Greg Stark <gsstark@mit.ed u> wrote:[color=blue]
>
> Bjørn T Johansen <btj@havleik.no > writes:
> [color=green]
> > Is it possible to alter field type (from varchar to text) without making a new
> > table?[/color]
>
> 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.[/color]

Does vacuum full completely remove the dropped column?

Thanks

--
Markus Bertheau <mbertheau@gmai l.com>

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

http://archives.postgresql.org

[Jeff Amiel]

upgrading minor versions

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.postgr esql.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

[Jan Wieck]

Re: upgrading minor versions

On 8/27/2004 12:21 PM, Jeff Amiel wrote:
[color=blue]
> 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.postgr esql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz>
> uncompressed...
> configure...[/color]

try

eval ./configure `/usr/bin/pg_config --configure`


Jan
[color=blue]
> 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[/color]


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Jeff Amiel]

Re: upgrading minor versions

Ok......still no dice.
still shows 7.4.2 when I select version()...
I must surely be doing something silly here...

Jan Wieck wrote:
[color=blue]
> On 8/27/2004 12:21 PM, Jeff Amiel wrote:
>[color=green]
>> 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.postgr esql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz>
>>
>> uncompressed...
>> configure...[/color]
>
>
> try
>
> eval ./configure `/usr/bin/pg_config --configure`
>
>
> Jan
>[color=green]
>> 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[/color]
>
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Doug McNaught]

Re: upgrading minor versions

Jeff Amiel <jamiel@istream imaging.com> writes:
[color=blue]
> So I downloaded postgresql-7.4.5.tar.gz
> <ftp://ftp21.us.postgr esql.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[/color]

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[Jeff Amiel]

Re: upgrading minor versions

Duh. I never restarted the server.
sheesh.
thanks to all.

Jeff

Doug McNaught wrote:
[color=blue]
>Did you shut down and restart the server after the upgrade? Are you
>sure the new binaries went where you think they did?
>
>-Doug
>
>[/color]


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

[Jan Wieck]

Re: upgrading minor versions

On 8/27/2004 12:44 PM, Jeff Amiel wrote:
[color=blue]
> Ok......still no dice.
> still shows 7.4.2 when I select version()...
> I must surely be doing something silly here...[/color]

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
[color=blue]
>
> Jan Wieck wrote:
>[color=green]
>> On 8/27/2004 12:21 PM, Jeff Amiel wrote:
>>[color=darkred]
>>> 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.postgr esql.org/pub/postgresql/v7.4.5/postgresql-7.4.5.tar.gz>
>>>
>>> uncompressed...
>>> configure...[/color]
>>
>>
>> try
>>
>> eval ./configure `/usr/bin/pg_config --configure`
>>
>>
>> Jan
>>[color=darkred]
>>> 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[/color]
>>
>>
>>[/color]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)[/color]


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

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

[Jeff Amiel]

Re: upgrading minor versions

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:
[color=blue]
> 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[/color]



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)