I have an addresses table with ZIP code field which has type VARCHAR. I need to select all addresses form this table using ZIP codes range. If I used next code:
select * from address where cast(zip as bigint) between 90210 and 90220
I get an error on fields where ZIP code cann't be cast as bigint.
How I can resolve this issue?
Derive a version of the zip column that is a bigint if the zip value is numeric, and null otherwise, and then test that.
So sth like
select * from address
where case when zip ~ '^[0-9]+$' then cast(zip as bigint) end between 90210 and 90220
To make this query efficient, you can create an index on this expression:
create index address_zip_idx on address(cast(zip as bigint)) where zip ~ '^[0-9]+$';
-- this query can now use that index
select * from address
where zip ~ '^[0-9]+$' and cast(zip as bigint) between 90210 and 90220;
The error is obvious: you have some zip addresses does not represent a number. Eg, letters or dashes. If you want to intepret them as numbers, you must then get rid of those that are not composed of digits ... if that really suits you. See araqnid's answer for some recipes. But first ask yourself, if you have some ZIP numbers store as "123-34" how do you want to intepret them, perhaps you need to rethink the format and the datatype.
If you have only US zip codes then you should probably have two int columns like zip and plus_4. But the simple answer to your question is to just do a varchar comparison. You probably already have the zip column indexed so this would still work quite well.
SELECT * FROM address WHERE zip BETWEEN '90210' AND '90220'