Thread: How to change data type in column ?

Hi,

How can I change a data type in column ?

Thnx.

On Tuesday 15 Apr 2003 10:36 am, Vitali Djatsuk wrote:
> Hi,
>
> How can I change a data type in column ?

Well, the closest you could get would be something like:

ALTER TABLE RENAME column TO backup_column;
ALTER TABLE ADD COLUMN column ...;
...copy and transform data...
ALTER TABLE DROP COLUMN backup_column;

Personally, I tend to dump/recreate the table where possible, but I might be a
little paranoid there.
I think you'll need to recreate any triggers/FK's in either case.
--
  Richard Huxton

On Tue, 15 Apr 2003, Vitali Djatsuk wrote:

>
> Hi,
>
> How can I change a data type in column ?

There are a lot of issues involved in changing the type.  I.e. what
should the database do to "coerce" the data from one type to another
that's already in the table.  should the misformed data in the input be
simply dropped and a null inserted, what if it's not null and doesn't fit,
do we toss the row?  not convert?
The best way to get around these problems is to select the fields into a
new table and coerce the type as you do it.  That way, you can see where
you're going before you get there.

create table test (name text, id int, balance numeric (12,2), num float);
insert some data...
select name, id, balance::float, num::numeric(12,2) into newtable;

Now you can check newtable, and if it's right you can then delete the old
table and rename newtable in its place.

> On Tue, 15 Apr 2003, Vitali Djatsuk wrote:
>
>>
>> Hi,
>>
>> How can I change a data type in column ?
>
> There are a lot of issues involved in changing the type.  I.e. what  should the database do to
> "coerce" the data from one type to another  that's already in the table.  should the misformed
> data in the input be  simply dropped and a null inserted, what if it's not null and doesn't
> fit,  do we toss the row?  not convert?
> The best way to get around these problems is to select the fields into a  new table and coerce
> the type as you do it.  That way, you can see where  you're going before you get there.
>
> create table test (name text, id int, balance numeric (12,2), num float); insert some data...
> select name, id, balance::float, num::numeric(12,2) into newtable;
>
> Now you can check newtable, and if it's right you can then delete the old  table and rename
> newtable in its place.

Instead of deleting the table
Why not add a new column  of the required type , update it with the  values
from old column , drop the old column and rename the new col to the old col .

Considering the fact that he already got too many references it may be easier
to avoid drop the table altogether.

regds
mallah.




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



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/