Re: CAST from VARCHAR to INT

"Luke Pascoe" <luke(dot)p(at)kmg(dot)co(dot)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