Thread: can't cast char to in
I have a table with a mileage column that is a character varying (please don't ask 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 :
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: "+"
How can I cast this?
Thanks!
On Apr 22, 2008, at 10:34 AM, blackwater dev wrote: > I have a table with a mileage column that is a character varying > (please don't ask 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: "+" > > How can I cast this? Well, you didn't really give any real information on the format of the data in your mileage column. However, my guess is that you've got at least one row with just '+' in the mileage column which is not the same thing as '+0'. You'll probably need to do a little data cleaning and, once that's done you should definitely consider switching that to an integer/numeric data type. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Yeah, it was my being stupid, I got it going now.
Thanks!
Thanks!
On Tue, Apr 22, 2008 at 11:42 AM, Erik Jones <erik@myemma.com> wrote:
Well, you didn't really give any real information on the format of the data in your mileage column. However, my guess is that you've got at least one row with just '+' in the mileage column which is not the same thing as '+0'. You'll probably need to do a little data cleaning and, once that's done you should definitely consider switching that to an integer/numeric data type.
On Apr 22, 2008, at 10:34 AM, blackwater dev wrote:I have a table with a mileage column that is a character varying (please don't ask 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: "+"
How can I cast this?
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
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