Here is my table:

                id      INT NOT NULL PRIMARY KEY,
                val     int4range[]

I want to index the val column:

ON      mytable
USING   GIN (INT4RANGE(val, '[]'));   // error, as is GIN(val)

I came up with the following:

CREATE OPERATOR CLASS gin_int4range_ops
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).

  • 1
    There is something weird in your question - I can run the CREATE OPERATOR CLASS statement successfully on my 9.2 test DB. – dezso Jul 30 '13 at 8:05
  • Yes, the create works. But try and run the CREATE INDEX command, or insert into the DB. I edited. – IamIC Jul 30 '13 at 8:09
  • 2
    Please try to indicat which steps are working and which are failing. And exactly what is the index definition which sometimes fails. ('Sometimes' means that you don't always start from the same point...) – dezso Jul 30 '13 at 8:47
  • Good point. Done. – IamIC Jul 30 '13 at 8:52
  • ERROR: 42883: function int4range(int4range[], unknown) does not exist – dezso Jul 30 '13 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.