Thread: BUG #1326: Unique and Primary Key index over bigint type doesn't work

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.


sislu=> \d x
              Table "public.x"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 id     | bigint                 | not null
 name   | character varying(100) |
 age    | integer                |
 bigid  | bigint                 |
Indexes:
    "x_pkey" primary key, btree (id)
    "un_x_age" unique, btree (age)
    "un_x_bigid" unique, btree (bigid)
    "un_x_name" unique, btree (name)

sislu=> explain select * from x where x.id = 12345;
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on x  (cost=0.00..22.50 rows=2 width=88)
   Filter: (id = 12345)
(2 rows)

sislu=> explain select * from x where x.bigid = 12345;
                    QUERY PLAN
---------------------------------------------------
 Seq Scan on x  (cost=0.00..22.50 rows=2 width=88)
   Filter: (bigid = 12345)
(2 rows)

sislu=> explain select * from x where x.name = 'asdf';
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using un_x_name on x  (cost=0.00..4.82 rows=2 width=88)
   Index Cond: ((name)::text = 'asdf'::text)
(2 rows)

sislu=> explain select * from x where x.age = 1;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using un_x_age on x  (cost=0.00..4.82 rows=2 width=88)
   Index Cond: (age = 1)
(2 rows)

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