Re: CAST from VARCHAR to INT

"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