When you aren't putting the quotes, the type of the numeric constant
gets int4 which won't match bigint so it doesn't realize it can
use the index. You can explicitly type it with ::bigint which should
make it use the index as well (current workaround until numeric types
get handled in a more happy way). For more details see archives.
On Tue, 22 May 2001, Dave Cramer wrote:
>
> Hi All,
>
> Below is a simple table with an index on phonenumber, if I do a select on
> phonenumber without quotes then it does a sequence scan?? If I do put
> quotes, then it does use the index scan??? Anyone know what's going on?
>
> The version of postgres is 7.1
>
> Regards,
>
> Dave
>
> test=# create table custbase (phonenumber int8, svctype char, svcchoice
> char, billtype char, ctype char);
> CREATE
> test=# create index custbaseidx on custbase (phonenumber);
> CREATE
> test=# \d custbase
> Table "custbase"
> Attribute | Type | Modifier
> -------------+--------------+----------
> phonenumber | bigint |
> svctype | character(1) |
> svcchoice | character(1) |
> billtype | character(1) |
> ctype | character(1) |
> Index: custbaseidx