pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCAST from VARCHAR to INT
7+ messages / 5 participants
[nested] [flat]
* CAST from VARCHAR to INT
@ 2003-01-24 02:56 Luke Pascoe <luke.p@kmg.co.nz>
0 siblings, 3 replies; 7+ messages in thread
From: Luke Pascoe @ 2003-01-24 02:56 UTC (permalink / raw)
To: pgsql-sql
(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
========================================
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CAST from VARCHAR to INT
@ 2003-01-24 05:58 Bhuvan A <bhuvansql@myrealbox.com>
parent: Luke Pascoe <luke.p@kmg.co.nz>
2 siblings, 1 reply; 7+ messages in thread
From: Bhuvan A @ 2003-01-24 05:58 UTC (permalink / raw)
To: Luke Pascoe <luke.p@kmg.co.nz>; +Cc: pgsql-sql
> (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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CAST from VARCHAR to INT
@ 2003-01-24 06:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Luke Pascoe <luke.p@kmg.co.nz>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-01-24 06:01 UTC (permalink / raw)
To: Luke Pascoe <luke.p@kmg.co.nz>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CAST from VARCHAR to INT
@ 2003-01-24 10:28 daq <daq@ugyvitelszolgaltato.hu>
parent: Luke Pascoe <luke.p@kmg.co.nz>
2 siblings, 1 reply; 7+ messages in thread
From: daq @ 2003-01-24 10:28 UTC (permalink / raw)
To: Luke Pascoe <luke.p@kmg.co.nz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CAST from VARCHAR to INT
@ 2003-01-24 15:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: daq <daq@ugyvitelszolgaltato.hu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-01-24 15:42 UTC (permalink / raw)
To: daq <daq@ugyvitelszolgaltato.hu>; +Cc: Luke Pascoe <luke.p@kmg.co.nz>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CAST from VARCHAR to INT
@ 2003-01-24 19:03 Roberto Mello <rmello@cc.usu.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Roberto Mello @ 2003-01-24 19:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: daq <daq@ugyvitelszolgaltato.hu>; Luke Pascoe <luke.p@kmg.co.nz>; pgsql-sql
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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CAST from VARCHAR to INT
@ 2003-01-27 20:11 Luke Pascoe <luke.p@kmg.co.nz>
parent: Bhuvan A <bhuvansql@myrealbox.com>
0 siblings, 0 replies; 7+ messages in thread
From: Luke Pascoe @ 2003-01-27 20:11 UTC (permalink / raw)
To: Bhuvan A <bhuvansql@myrealbox.com>; +Cc: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-01-27 20:11 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-24 02:56 CAST from VARCHAR to INT Luke Pascoe <luke.p@kmg.co.nz>
2003-01-24 05:58 ` Bhuvan A <bhuvansql@myrealbox.com>
2003-01-27 20:11 ` Luke Pascoe <luke.p@kmg.co.nz>
2003-01-24 06:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-24 10:28 ` daq <daq@ugyvitelszolgaltato.hu>
2003-01-24 15:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-24 19:03 ` Roberto Mello <rmello@cc.usu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox