In PostgreSQL 9.2 I had no problem creating the an index that had both a geography (postGIS) type and and integer as a compound index. But now (9.6) it complains on creation of the index and I don't understand the hint it is providing:
The columns and data are all created properly, Postgres is complaining on the create index.
ERROR: data type integer 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.
********** Error**********
ERROR: data type integer has no default operator class for access method "gist"
SQL state: 42704
Hint: You must specify an operator class for the index
or define a default operator class for the data type.
The schema definition is as follows:
- Table: portal.inventory
-- DROP TABLE portal.inventory;
CREATE TABLE portal.inventory
(
type character varying,
pid integer,
size bigint,
date timestamp without time zone,
path character varying,
outline geography(Polygon,4326)
)
WITH (
OIDS=FALSE
);
ALTER TABLE portal.inventory
OWNER TO postgres;
-- Index: portal.inventory_compound_idx
-- DROP INDEX portal.inventory_compound_idx;
CREATE INDEX inventory_compound_idx
ON portal.inventory
USING gist
(outline, pid);
-- Index: portal.inventory_icompound_idx
-- DROP INDEX portal.inventory_icompound_idx;
CREATE INDEX inventory_icompound_idx
ON portal.inventory
USING gist
(pid, outline);
Best Answer
You need to install a specific
EXTENSION
in your database:According to PostgreSQL documentation on btree_gist:
(emphasis mine)
btree_gist
is part of the standard (current) PostgreSQL installation, so, you don't actually need to install any files in your system.After installing this extension, you can execute all these instructions on a clean install of PostgreSQL 9.6.2, without a glitch:
And execute all your
CREATE
statements without a glitch.NOTE: This was also needed for version 9.2, according to comment from @Erwin Brandstetter. So, most probably, if you make a dump of the version 9.2 databse, the
CREATE EXTENSION btree_gist ;
statement should appear.