I'd like to query a series of integer columns which look like this: 10, 1010, 101010, 201010
I want all the ones which start with 10. Changing them to string columns is not an option.
My feeling is that this is potentially achievable using bitstring operators: http://www.postgresql.org/docs/9.3/static/functions-bitstring.html
You don't need to "change" them to string columns. Just cast for the sake of the test:
SELECT *
FROM tbl
WHERE left(int_col::text, 2) = '10';
Or, even more succinct:
...
WHERE int_col::text LIKE '10%';
That's the appropriate test, too, since your condition is based on the decimal string representation of the number.
This may look stupid, but it may trick the optimiser into using an index (if one is available)
SELECT *
FROM tbl
WHERE int_col = 10
OR int_col/10 = 10
OR int_col/100 = 10
OR int_col/1000 = 10
OR int_col/10000 = 10
OR int_col/100000 = 10
OR int_col/1000000 = 10
OR int_col/10000000 = 10
-- ...
;
int_col = ANY ('{10, 100, 1000, 10000, ...}'::int[]), which is sargable. On the other hand, if you need this fast, CREATE INDEX ON tbl (cast(int_col AS text)); and WHERE int_col::text LIKE '10%' will be faster. Or even more specialized: CREATE INDEX ON tbl (left(cast(int_col AS text), 2)); ...
int_col IN (10, 100, 1000, 10000, ...). Postgres translates either to int_col = 10 OR int_col = 100 OR .. internally.