PostgreSQL/PostGIS 9.6 打破了我的複合索引

在 PostgreSQL 9.2 中，創建一個同時具有地理（postGIS）類型和整數作為複合索引的索引沒有問題。但是現在（9.6）它抱怨創建索引，我不明白它提供的提示：

列和數據都已正確創建，Postgres 抱怨創建索引。

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.

架構定義如下：

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

您需要在數據庫中安裝特定EXTENSION的：

CREATE EXTENSION btree_gist ;

根據btree_gist 上的 PostgreSQL 文件：

btree_gist 提供 GiST 索引操作符類，這些操作符類為 int2、int4、int8、float4、float8、數字、帶時區的時間戳、不帶時區的時間戳、帶時區的時間、不帶時區的時間、日期的數據類型實現 B 樹等效行為、interval、oid、money、char、varchar、text、bytea、bit、varbit、macaddr、inet 和 cidr。

一般來說，這些運算符類不會勝過等效的標準 B-tree 索引方法，並且它們缺少標準 B-tree 程式碼的一個主要特性：強制唯一性的能力。但是，它們提供了一些 B 樹索引不具備的其他功能，如下所述。此外，當需要多列 GiST 索引時，這些運算符類很有用，其中一些列是只能用 GiST 索引的數據類型，而其他列只是簡單的數據類型。最後，這些操作符類對於 GiST 測試很有用，也可以作為開發其他 GiST 操作符類的基礎。

（強調我的）

btree_gist是標準（目前）PostgreSQL 安裝的一部分，因此，您實際上不需要在系統中安裝任何文件。

安裝此擴展後，您可以在全新安裝的 PostgreSQL 9.6.2上執行所有這些指令，而不會出現故障：

-- If there is not there, create extension PostGis as well
CREATE EXTENSION IF NOT EXISTS postgis ;

-- Create Schema `portal`
CREATE SCHEMA IF NOT EXISTS portal ;

並順利執行您CREATE的所有語句。

CREATE TABLE portal.inventory
(
 type character varying,
 pid integer,
 size bigint,
 date timestamp without time zone,
 path character varying,
 outline geography(Polygon,4326)
);

CREATE INDEX inventory_compound_idx
 ON portal.inventory
 USING gist
 (outline, pid);

CREATE INDEX inventory_icompound_idx
 ON portal.inventory
 USING gist
 (pid, outline);

---

注意：根據@Erwin Brandstetter 的評論，9.2 版也需要這樣做。因此，最有可能的是，如果您轉儲9.2 版數據庫，則CREATE EXTENSION btree_gist ;應該出現該語句。

引用自：https://dba.stackexchange.com/questions/168822