Postgres Pro
Downloads
Home   >   mailing lists

Thread: CAST from VARCHAR to INT

CAST from VARCHAR to INT

From
"Luke Pascoe"
Date:
(Postgres 7.2.1)

I screwed up when I was designing a table a while back and made a column a
VARCHAR that referenced (and should have been) an INT.

Now I'm trying to correct my mistake, I've created a new table and I'm
trying to INSERT INTO...SELECT the data into it, but it's complaining that
it can't stick a VARCHAR into an INT. All the values in the column are valid
integers (the foreign key sees to that) but even a CAST won't do it.

How can I force it to copy/change the values?

========================================
Luke Pascoe
Senior Developer / Systems administrator
KMG (NZ) Limited. http://www.kmg.co.nz
Mobile: (021) 303019
Email: luke.p@kmg.co.nz
========================================

Re: CAST from VARCHAR to INT

From
Bhuvan A
Date:
> (Postgres 7.2.1)
> 
> I screwed up when I was designing a table a while back and made a column a
> VARCHAR that referenced (and should have been) an INT.
> 
> Now I'm trying to correct my mistake, I've created a new table and I'm
> trying to INSERT INTO...SELECT the data into it, but it's complaining that
> it can't stick a VARCHAR into an INT. All the values in the column are valid
> integers (the foreign key sees to that) but even a CAST won't do it.
> 
> How can I force it to copy/change the values?
> 

varchar cannot be casted to integer directly. Rather we can do it this 
way:

=> select your_varchar_field::text::int from your_table;

regards,
bhuvaneswaran

Re: CAST from VARCHAR to INT

From
Tom Lane
Date:
"Luke Pascoe" <luke.p@kmg.co.nz> writes:
> Now I'm trying to correct my mistake, I've created a new table and I'm
> trying to INSERT INTO...SELECT the data into it, but it's complaining that
> it can't stick a VARCHAR into an INT. All the values in the column are valid
> integers (the foreign key sees to that) but even a CAST won't do it.

I think you need to cast via TEXT.

regression=> select 'z'::varchar::int;
ERROR:  Cannot cast type character varying to integer
regression=> select 'z'::varchar::text::int;
ERROR:  pg_atoi: error in "z": can't parse "z"
regression=> select '42'::varchar::text::int;int4
------  42
(1 row)

        regards, tom lane

Re: CAST from VARCHAR to INT

From
daq
Date:
Hello!

Like others said you can't cast varchar to int directly.
Make your life easier! :) You must write a function like
this:

create function "int4"(character varying) returns int4 as '      DECLARE             input alias for $1;      BEGIN
     return (input::text::int4);      END;
 
' language 'plpgsql';

When you try the cast varchar_field::integer or varchar_field::int4 Postgres call
the function named int4 and takes varchar type parameter.


DAQ

Re: CAST from VARCHAR to INT

From
Tom Lane
Date:
daq <daq@ugyvitelszolgaltato.hu> writes:
> Make your life easier! :) You must write a function like
> this:

> create function "int4"(character varying) returns int4 as '
>        DECLARE
>               input alias for $1;
>        BEGIN
>             return (input::text::int4);
>        END;
> ' language 'plpgsql';

> When you try the cast varchar_field::integer or varchar_field::int4 Postgres call
> the function named int4 and takes varchar type parameter.

Note that as of 7.3 you need to issue a CREATE CAST command; the name of
the function is not what drives this anymore.  (Though following the old
naming convention that function name == return type still seems like a
good idea.)
        regards, tom lane

Re: CAST from VARCHAR to INT

From
Roberto Mello
Date:
On Fri, Jan 24, 2003 at 10:42:29AM -0500, Tom Lane wrote:
> daq <daq@ugyvitelszolgaltato.hu> writes:
> > Make your life easier! :) You must write a function like
> > this:
> 
> > create function "int4"(character varying) returns int4 as '
> >        DECLARE
> >               input alias for $1;
> >        BEGIN
> >             return (input::text::int4);
> >        END;
> > ' language 'plpgsql';
> 
> > When you try the cast varchar_field::integer or varchar_field::int4 Postgres call
> > the function named int4 and takes varchar type parameter.
> 
> Note that as of 7.3 you need to issue a CREATE CAST command; the name of
> the function is not what drives this anymore.  (Though following the old
> naming convention that function name == return type still seems like a
> good idea.)

Wow, I didn't even know of either of these features. Sounds useful.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
Friends encourage friends to use Win(e)dows - under Linux!

Re: CAST from VARCHAR to INT

From
"Luke Pascoe"
Date:
Sweet, worked the charm, thanks!

P.S. Anyone know why it takes several hours[1] for my posts to come through
the list?

[1] Posted a message at ~9am friday, it got back to me ~4pm!

----- Original Message -----
From: "Bhuvan A" <bhuvansql@myrealbox.com>
To: "Luke Pascoe" <luke.p@kmg.co.nz>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, January 24, 2003 6:58 PM
Subject: Re: [SQL] CAST from VARCHAR to INT


>
> > (Postgres 7.2.1)
> >
> > I screwed up when I was designing a table a while back and made a column
a
> > VARCHAR that referenced (and should have been) an INT.
> >
> > Now I'm trying to correct my mistake, I've created a new table and I'm
> > trying to INSERT INTO...SELECT the data into it, but it's complaining
that
> > it can't stick a VARCHAR into an INT. All the values in the column are
valid
> > integers (the foreign key sees to that) but even a CAST won't do it.
> >
> > How can I force it to copy/change the values?
> >
>
> varchar cannot be casted to integer directly. Rather we can do it this
> way:
>
> => select your_varchar_field::text::int from your_table;
>
> regards,
> bhuvaneswaran
>
>
>
>