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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of