Index an array of custom range type in PostgreSQL

Question

I've created a custom range type:

CREATE OR REPLACE FUNCTION smallint_subtype_diff(x smallint, y smallint) RETURNS float8 AS
'SELECT (x - y)::float8' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE smallintrange AS RANGE (
    subtype = smallint,
    subtype_opclass = int2_ops,
    subtype_diff = smallint_subtype_diff    -- required by GiST
);

It works fine and I can index it with GiST.

However, I now wish to index an array of this type: smallintrange[]

Regardless of whether I try GiST or GIN, I get this:

CREATE INDEX ix_Vendors_OpenTimes ON public.Vendors USING gin (OpenTimes);

ERROR: data type smallintrange[] has no default operator class for access method "gin" HINT: You must specify an operator class for the index or define a default operator class for the data type.

How do I get this to work?

pozs · Accepted Answer · 2016-04-07 08:16:41Z

2

The same error message is thrown when you try to index any built-in range types, f.ex. int4range. This is because there is no built-in GIN operator class for array of ranges. (There is no operator class for GiST or SP-GiST either).

You should try to re-structure your data, f.ex. into a one-to-many relationship, where each vendors row can have multiple entry in a (let's say) vendor_ranges, where you can use a simple range (not an array of them). In that context you could index your ranges.

answered Apr 7, 2016 at 8:16

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IamIC Over a year ago

Thank you. I'm sure it could be extended, but that's a whole other level.

Collectives™ on Stack Overflow

Index an array of custom range type in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related