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
