Tweeted twitter.com/StackDBAs/status/848383142268456961
2 added 78 characters in body; edited tags; edited title
source | link

PostGRESQL/. PostgreSQL/PostGIS 9.6 broke my compound index

In PostGreSQLPostgreSQL 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, PGPostgres 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.

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

PostGRESQL/./PostGIS 9.6 broke my compound index

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, PG 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.

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

PostgreSQL/PostGIS 9.6 broke my compound index

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);
1
source | link

PostGRESQL/./PostGIS 9.6 broke my compound index

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, PG 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.

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