PostgreSQL: ERROR – data type character varying has no default operator class for access method “gist”

If we want to store range of data, we have very good options RANGE DATATYPE in PostgreSQL.

PostgreSQL: Example of RANGE Data Type

In one of our reporting servers, require to store a range of date bases on unique log code.
Means, one unique code cannot be duplicated with the same range of dates.

We can store a range of dates using TSRANGE DATATYPE, but I have to also add one constraint which checks the unique combination of value and range.

Here, I have taken an example of the Student table where the combination of Stud Name and Duration should be unique.
Try to Create below Student table:

While creating above table with EXCLUDE Range constraint,I was getting below error .
The solution is to create btree_gist EXTENSION.
Insert few valid sample records:
Try to Insert Same Student Name with same range:
You will get an error like, because we have added constraint like, no duplicate range with the same Student Name.
Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of