I'd like to create index for PostGIS db in Postgresql.
This is my table (simplified):
CREATE EXTENSION postgis;
CREATE EXTENSION btree_gist;
CREATE TABLE table1 (id int, data boolean);
SELECT AddGeometryColumn('table1', 'geom', 900913, 'linestring', 2);
When I create the index:
CREATE INDEX index1 ON table1 USING gist(data, geom);
it failed with error message:
ERROR: data type boolean 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.
The problem is btree_gist doesn't support boolean
type. It did work with other types like int
.
But I cannot change the type from boolean
to int
because above is simplified. In my original issue, what I want is index on expression and I cannot modify the (program generated) expression.