postgres create an index
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 tab开发者_StackOverflow社区le:
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.
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.
精彩评论