Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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
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 posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.