Thread

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

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-19T14:25:17Z

   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)
   
   
   
   

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

   Richard Huxton <dev@archonet.com> — 2004-11-19T15:52:19Z

   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