Re: Is indexing broken for bigint columns?

> -----Original Message-----
> From: Peter Eisentraut [mailto:peter_e(at)gmx(dot)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?