Skip site navigation (1) Skip section navigation (2)

Re: Is indexing broken for bigint columns?

From: "Dann Corbit" <DCorbit(at)connx(dot)com>
To: "Peter Eisentraut" <peter_e(at)gmx(dot)net>,"PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Is indexing broken for bigint columns?
Date: 2004-02-25 00:20:34
Message-ID: D90A5A6C612A39408103E6ECDD77B829408D0F@voyager.corporate.connx.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> -----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?

Responses

pgsql-hackers by date

Next:From: Mike MascariDate: 2004-02-25 00:37:14
Subject: Re: Is indexing broken for bigint columns?
Previous:From: Peter EisentrautDate: 2004-02-24 23:38:23
Subject: Re: Is indexing broken for bigint columns?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group