CODEWITHSUNDEEP
sqlpostgresqltypespattern-matching
Mispy
Mispy

Reputation: 879

Postgres query for digits at the start of an integer column

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

Answers (2)

wildplasser
wildplasser

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

Erwin Brandstetter
Erwin Brandstetter

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

Related Questions