Data type variable has no default operator class for "gist" accessor method
I tried the following command (Postgresql):
ALTER TABLE authentication ADD CONSTRAINT overlapping_times EXCLUDE USING GIST
(method with =,
authenticator with =,
box(point(extract(epoch FROM validfrom at time zone 'UTC'),extract(epoch FROM validfrom at time zone 'UTC') ),
point(extract(epoch FROM validuntil at time zone 'UTC'), extract(epoch FROM validuntil at time zone 'UTC'))) WITH &&
)
and I got the following error message:
ERROR: data type character varying 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.
I'm googling pretty extensively, but I can't seem to translate this into plain English. What should I do to execute the command above? The "method" type has a different character, "authenticator" is text, "validfrom", "validuntil" are dates.
source to share
For authenticator
and, method
use a simple unique constraint. text and varchar () are identical for indexing purposes.
This means three alternative table operators instead of one, but that should save you these problems. Box must support GiST properly for you to be there well.
In plain English, the error tells you that the data type does not support the index operations expected from the index type. So text strings cannot be found using the index, such as whether they overlap. In other words, three cannot be put into the same constraint.
Also, keep in mind that UNIQUE constraints are faster than constraint exceptions, so it is preferable when they work.
source to share