Re: Bugs in bigint indexes

MessageTry this

 explain SELECT id FROM it_test WHERE id='123';
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using it_test_pkey on it_test  (cost=0.00..4.82 rows=1 width=8)
   Index Cond: (id = 123::bigint)
(2 rows)

The point is that 123 is naturally considered by Postgres to be an int4. If you want to specify it is an int8 then use quotes around your value or an explicit cast if the sought value is some kind of int4, int2 attribute.

The same apply for smallints.


  ----- Original Message ----- 
  From: Gustavo Scotti 
  To: pgsql-bugs@postgresql.org 
  Sent: Wednesday, December 17, 2003 8:56 PM
  Subject: [BUGS] Bugs in bigint indexes
  I almost got nuts those two days I'm stuck with this issue... 
  Let's get straight to the point. I'm using a small portion of my actual table, but this is enough.

  CREATE SEQUENCE it_seq;
  CREATE TABLE it_test (
   id   bigint not null primary key default nextval('public.it_test_id_seq'::text)
  );

  explain SELECT id FROM it_test WHERE id=123;
  Seq Scan on it_test (cost=0.0..22.50 rows=2 width=8)
     Filter: (id=123)
  (2 rows)