Here is my table:
CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range );
I want to index the val column:
CREATE INDEX ix_mytable_val ON mytable USING GIN (INT4RANGE(val, '')); // error, as is GIN(val)
I came up with the following:
CREATE OPERATOR CLASS gin_int4range_ops DEFAULT FOR TYPE int4range USING gin AS OPERATOR 1 <(anyrange,anyrange), OPERATOR 2 <=(anyrange,anyrange), OPERATOR 3 =(anyrange,anyrange), OPERATOR 4 >=(anyrange,anyrange), OPERATOR 5 >(anyrange,anyrange), FUNCTION 1 lower(anyrange), FUNCTION 2 upper(anyrange), FUNCTION 3 isempty(anyrange), FUNCTION 4 lower_inc(anyrange), FUNCTION 5 upper_inc(anyrange);
But when I try to create the index, it fails (error below). However, if I call the create from within a DO $$ block, it executes.
If the create index executed, I get the error on INSERT INTO instead.
"ERROR: cache lookup failed for type 1"
I also tried this:
OPERATOR 1 &&(anyrange,anyrange), OPERATOR 2 <@(anyrange,anyrange), OPERATOR 3 @>(anyrange,anyrange), OPERATOR 4 =(anyrange,anyrange),
In order to try and solve this, I have rebooted PG, the machine, and vacuumed the DB. I believe there is an error in the CREATE OPERATOR code.
If I can index an array of custom type of (int, int4range), that would be even better.
I've spent quite some time (a full day) wading through documentation, forums, etc., but can find nothing that really helps me to understand how to solve this (i.e. create a working custom operator class).