"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