Using postgresql gin or gist index with bigint column

Question

i am trying to create gin index on bigint column and getting an error (PostgreSQL 9.1.9 / Debian 7).

CREATE TABLE test (id bigint CONSTRAINT test_pkey PRIMARY KEY, field bigint);

CREATE INDEX idx_test_field ON test using GIN(field);

ERROR:  data type bigint has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Is there no default support for int8 gin,gist indexes ?

Why do you want to build gin or gist indexes on bigint? Those indexes are for specialized data structures such as geometries, intarray etc.. A simple B-tree index supports >,<,= which is basically all the functionality you need for BIGINTS. — Alexandros, May 30 '14 at 07:43

score 27 · Accepted Answer · answered May 30 '14 at 07:52

There's generally no reason to create a GiST or GIN index on a primitive type.

If you do require this - say, if you want a composite index that includes both some primitive types and some more complex GiST / GIN-only index types - then you will want the btree_gist or btree_gin modules, as appropriate.

CREATE EXTENSION btree_gin;

Using postgresql gin or gist index with bigint column

1 Answers1