How to define operator class for composite type in PostgreSQL?

Question

I have a composite type. And I want to define exclusion constraint on it, that would also be combined with range exclusions, but getting the following error.

create type example_t as (
    x uuid,
    y text
);

create table example (
    id example_t not null,
    time tstzrange not null,

    exclude using gist (id with =, time with &&)
);

ERROR: data type example_t 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. SQL state: 42704

How can I define the operator class for 'example_t' composite type?

Laurenz Albe · Accepted Answer · 2018-06-25 15:44:34Z

2

It is complicated to define a new GiST operator class. You'd have to define support functions and matching strategies. See the documentation for an example how that is done using C functions.

But I think it would be much simpler not to include the column of type example_t in the exclusion constraint, but the individual elements id.x and id.y. That way you can probably get along with the operator classes defined in the btree_gist contrib module.

answered Jun 25, 2018 at 15:44

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

boxx Over a year ago

Yes, it's probably the simplest I might do.

boxx Over a year ago

I've just hoped that where is a way to reuse operators of uuid and text classes to do so.

Collectives™ on Stack Overflow

How to define operator class for composite type in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related