> -----Original Message----- > From: Peter Eisentraut [mailto:[EMAIL PROTECTED] > 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? ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html