Re: BUG #1326: Unique and Primary Key index over bigint type

PostgreSQL Bugs List wrote:
> The following bug has been logged online:
> 
> Bug reference:      1326
> Logged by:          Fernando Kasten Peinado
> 
> Email address:      fernandopeinado@uol.com.br
> 
> PostgreSQL version: 7.4.5
> 
> Operating system:   Linux RedHat 7.3
> 
> Description:        Unique and Primary Key index over bigint type doesn't 
> work 
> 
> Details: 
> 
> index is not used when Type is bigint.

This is a known issue with versions prior to 8.0. When you provide a 
constant integer smaller than 2^31 it assumes you want int4. Of course, 
the planner then looks for an int4 index and can't find one.

The solution/workaround is to make sure you specify the type of your 
constant, or at least make sure it doesn't look like an int4.
   select * from x where x.id = 12345::int8;
   select * from x where x.id = CAST(12345 AS int8);
   select * from x where x.id = '12345';

The last works because '...' is type unknown so it looks at x.id to see 
what type it wants and casts for you.

HTH

-- 
   Richard Huxton
   Archonet Ltd