How to create gist index on boolean type?

Question

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.

Answer 1

Even if you're using a functional index there's no reason you cannot build it, you just need to wrap your boolean output in a casting function:

CREATE INDEX funcindex ON foobar USING GIST (int4(foo>'bar'));

Now you just have to make sure your query uses the exact expression in your index:

SELECT * FROM funcindex WHERE int4(foo>'bar') = 0;

So for your example from your question (1 is true, 0 is false):

CREATE INDEX index1 ON table1 USING gist(int4(data), geom);
SELECT * FROM table1 WHERE int4(data) = 1 AND ST_Intersects(geom, ...)

(Side note: you may not find multi-key indexes super effective in GIST, make sure you do some real testing of all the alternatives for performance before settling on them. I've seen them work both very well, and not.)

Answer 2

A few things

1. AddGeometeryColumn() is old and should not be used anymore. If you're using a newer version (2.0+>) of PostGIS use ALTER TABLE instead.

2. From the docs,

   Adds a geometry column to an existing table of attributes. The schema_name is the name of the table schema. The srid must be an integer value reference to an entry in the SPATIAL_REF_SYS table. The type must be a string corresponding to the geometry type, eg, 'POLYGON' or 'MULTILINESTRING'. An error is thrown if the schemaname doesn't exist (or not visible in the current search_path) or the specified SRID, geometry type, or dimension is invalid.

3. It doesn't make any sense, afaik, to store a GEOMETRY type in bool. PostGIS types are TOASTed. A bool is one byte inline in the row's page, never toasted. Even a ST_Point is 32 bytes...

   SELECT * FROM pg_column_size( ST_POINT(0,0) );
   pg_column_size 
   ----------------
                32
   

On another reading, it doesn't make any sense to have a gist index on a bool type. If you meant to have a partial gist index. You can create one like this

CREATE TABLE foo AS
SELECT true AS bar, ST_MakePoint(0,0)::geometry AS geom;

CREATE INDEX ON foo USING gist( geom ) WHERE bar;