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);