> -----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?
