Thread: create new field

Hello

I would like to change the type of a column. At the moment, it is varchar(4)
but I would like it to be int. All values in the field at the moment are
actually integer.

I tried a way I had seen in the archives - it foes along the lines of adding
a column, using update, drop the old column and rename the new one.

alter table tab add column new_col int4;
update tab set new_col = "OLD_COL";
ERROR:  column "new_col" is of type integer but expression is of type
characte
r       You will need to rewrite or cast the expression

OK - so I tried casting.

template1=# update tab set new_col = "OLD_COL"::int4;
ERROR:  Cannot cast type character to integer

I understand this - some tables might have characters in the varchar but how
to get around it in my case ? I know that my character field has only
integers in it ?

Thanks

Chris

Chris,

> template1=# update tab set new_col = "OLD_COL"::int4;
> ERROR:  Cannot cast type character to integer
>
> I understand this - some tables might have characters in the varchar but
> how to get around it in my case ? I know that my character field has only
> integers in it ?

Actually, you just need to use the to_number function as an intermediary:

UPDATE tab SET new_col = CAST(to_number("OLD_COL", '9999') AS INT);

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, Oct 06, 2003 at 17:35:11 +0100, Chris Faulkner <chrisf@oramap.com> wrote:
> 
> OK - so I tried casting.
> 
> template1=# update tab set new_col = "OLD_COL"::int4;
> ERROR:  Cannot cast type character to integer
> 
> I understand this - some tables might have characters in the varchar but how
> to get around it in my case ? I know that my character field has only
> integers in it ?

You want to use to_number to do the conversion.

Hello

Can anyone tell me - is there a system table or view that I can query to
show all current sessions ?

Thanks


Chris

SELECT * FROM pg_stat_activity;<br /><br /> On Fri, 2003-10-10 at 09:48, Chris Faulkner wrote: <blockquote
type="CITE"><pre><fontcolor="#737373"><i>Hello 

Can anyone tell me - is there a system table or view that I can query to
show all current sessions ?

Thanks


Chris



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
</i></font></pre></blockquote>