Reputation: 879
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
Upvotes: 1
Views: 3819
Reputation: 44250
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
-- ...
;
Upvotes: 1
Reputation: 659367
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.
Upvotes: 4