postgres create an index

Question

I am going from mysql to postgres and I am having a problem creating an index.

CREATE INDEX pointsloc ON table USING gist (point_col);

This is the response I get back:

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

I have seen I need to specify the operator class for the index, different classes can be used depending upon the type of operators you wish to use on the column. I wish to use the @> or ~ to find if a point is within a polygon.

How do i specify the operator class?? help please has to be a simple thing but I am stumped!

EDIT

Below is a print screen of me trying to add an index to the branch table:

                                   Table "public.branch"
      Column      |       Type       |                      Modifiers                      
------------------+------------------+-----------------------------------------------------
 id               | integer          | not null default nextval('branch_id_seq'::regclass)
 name             | character(120)   | 
 center_point_lat | double precision | 
 center_point_lng | double precision | 
 center_point     | point            | 
Indexes:
    "branch_pkey" PRIMARY KEY, btree (id)

paul=# create index pt_idx on branch using gist (center_point);
ERROR:  data type point has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Denis de Bernardy · Accepted Answer · 2011-07-10 14:40:21Z

3

Seems to be working fine when I try:

test=# create table test (pt point);
CREATE TABLE
test=# create index pt_idx on test using gist (pt);
CREATE INDEX

Are you sure your point_col actually is of type point? Because, if it's a varchar, than it will indeed miserably fail without the btree_gist contrib - and even then it won't be very useful.

answered Jul 10, 2011 at 14:40

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Blu Towers Over a year ago

edited my question showing the print screen and me trying to add a index...please tell me i am being stupid :)

Denis de Bernardy Over a year ago

It might be your Postgresql version. (I'm running 9.1-beta, which has additions to gist indexing, so that might be why I'm not getting any errors.)

Blu Towers Over a year ago

That will probably be it i am using 8.1.23 on a Centos 5.5 system. Doing a yum update shows I cant update to the newer postgres version....:(, any ideas as to how it would have been done with 8.1.23. I cant seem to find anywhere info to add the operator classes when creating the index or even which ones i would need to do the polygon/point search lol

Denis de Bernardy Over a year ago

Update: in 9.0, the documentation's examples suggest that you create an index on box(point_col, point_col).

Denis de Bernardy Over a year ago

PG 8.1 is over 6 years old. That's not just outdated. It's hopelessly so. :-|

|

Collectives™ on Stack Overflow

postgres create an index

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related