Re: can't cast char to in

blackwater dev wrote:
> I have a table with a mileage column that is a character varying (please
> don't ask why :).

Why? :-)

> I need to do a query where mileage > 500
>
> select * from cars where mileage>500
>
> So I need to cast it but everything I try throws an error such as :
>
> ERROR: invalid input syntax for integer: "+"

Once you've cleaned your data, I would do one of two things:

1. Add a constraint to restrict the values the mileage column will accept:
ALTER TABLE cars ADD CONSTRAINT valid_mileage
CHECK (mileage ~ '^[+]?[0-9]+$');

2. You can alter the type on-the-fly too:
ALTER TABLE mileage_test ALTER COLUMN mileage TYPE integer
USING (mileage::int);

Note that you'll need to remove the constraint from #1 if you've applied
that.

--
Richard Huxton
Archonet Ltd