> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e@gmx.net] 
> Sent: Tuesday, February 24, 2004 3:38 PM
> To: Dann Corbit; PostgreSQL-development
> Subject: Re: [HACKERS] Is indexing broken for bigint columns?
> 
> 
> Dann Corbit wrote:
> > http://www.phpbuilder.com/columns/smith20010821.php3?page=3
> 
http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT

PostgreSQL is the only database that requires casts to do an index
lookup.

This is SQL*Server syntax:
==============================================================
drop table foo
go

create table foo (bar bigint)
go

insert into foo (bar) values (1)
go

insert into foo (bar) values (-9223372036854775808)
go

insert into foo (bar) values (9223372036854775807)
go

create unique clustered index foobar on foo(bar)
go

select * from foo where bar = 1
Go
-- Correctly returns a value of 1.
==============================================================
This is Oracle syntax:
==============================================================
SQL> drop table foo;

Table dropped.

SQL> 
SQL> create table foo (bar number(19));

Table created.

SQL> 
SQL> insert into foo (bar) values (1);

1 row created.

SQL> 
SQL> insert into foo (bar) values (-9223372036854775808);

1 row created.

SQL> 
SQL> insert into foo (bar) values (9223372036854775807);

1 row created.

SQL> 
SQL> create unique index foobar on foo(bar);

Index created.

SQL> 
SQL> select * from foo where bar = 1;

      BAR
---------
        1

SQL> 
SQL> 
==============================================================

DB/2 uses bigint like SQL*Server and PostgreSQL and necessary
conversions are implicit.
Sybase and Rdb also use bigint types.

And now, here is the unkindest cut of all:

mysql> create table foo (bar bigint);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into foo (bar) values (1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into foo (bar) values (-9223372036854775808);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> insert into foo (bar) values (9223372036854775807);
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> create unique index foobar on foo(bar);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from foo where bar = 1;
+------+
| bar  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

And (prattling on) if this is necessary for PostgreSQL:
select * from foo where bar = 1::bigint;

Why wouldn't this be necessary:
select * from foo where bar = 1::integer;
For an integer column?

Tue, 24 Feb 2004 16:20:34 -0800
"Dann Corbit" <DCorbit@connx.com>
Re: Is indexing broken for bigint columns?
Dann Corbit wrote:

> PostgreSQL is the only database that requires casts to do an index
> lookup.

Possibly (quite probably) true, but you don't show any evidence that 
SQL*Server, Oracle, or MySQL uses indexes either. Like I said 
before, Tom (of course) already has a fix is already in the 
development branch:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=29832.1068682253%40sss.pgh.pa.us&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26scoring%3Dd%26q%3Dbigint%2Bindex%2Bhackers%2Bpostgresql

> 
> This is SQL*Server syntax:
> ==============================================================
...
> select * from foo where bar = 1
...
> This is Oracle syntax:
> ==============================================================
> SQL> select * from foo where bar = 1;
...
> mysql> select * from foo where bar = 1;

Mike Mascari




Tue, 24 Feb 2004 19:37:14 -0500
Mike Mascari <mascarm@mascari.com>
Re: Is indexing broken for bigint columns?
Dann Corbit wrote:
> > Dann Corbit wrote:
> > > http://www.phpbuilder.com/columns/smith20010821.php3?page=3
>
> http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-INT
>
> PostgreSQL is the only database that requires casts to do an index
> lookup.

This issue has been discussed on these mailing lists literally dozens of 
times.  If you're interested in the details, please see the archives.  
Further discussion will hopefully not be necessary, because 7.5 will 
fix it.


Wed, 25 Feb 2004 01:38:31 +0100
Peter Eisentraut <peter_e@gmx.net>
Re: Is indexing broken for bigint columns?