Error getting when creating gin index on jsonb column postgresql9.5

Question

I have product_reviews TABLE with product_review jsonb column.

jsonb column : [{"comment": [{"condition": "Good", "Entered": "true"}], "productid": 321}]

CREATE INDEX idx_product_reviews_product_review ON product_reviews  USING gin (CAST (product_review ->> 'productid') AS bigint )   ;

So when i try to create gin index on this jsonb column, i get error.

ERROR:  data type bigint 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.

I want to index productid as bigint type values, so i can get jsonb "comment" values comparing this productid.

for functional index you shoul to use classic btree index - don't use gin — Pavel Stehule
– Pavel Stehule, Commented Feb 19, 2017 at 17:24
Usually GIN,GiST indexes are used for types where operation < or > is not defined. When these operations are defined, then you can use a BTREE index. For almost all types the GIN, GIST index is supported or BTREE index is supported - not both cases. — Pavel Stehule
– Pavel Stehule, Commented Feb 19, 2017 at 18:57
a arrays can have GIN or GiST indexes. These indexes has different behave in dependency to result length and array length. You should to check corner cases. — Pavel Stehule
– Pavel Stehule, Commented Feb 19, 2017 at 20:49

arunpandianp · Accepted Answer · 2019-03-23 00:28:09Z

0

btree_gin https://www.postgresql.org/docs/9.5/btree-gin.html extension provides gin operator classes for many data types, including bigint.

create the index after enabling btree_gin extension using

CREATE EXTENSION btree_gin;

answered Mar 23, 2019 at 0:28

arunpandianp

1462 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Error getting when creating gin index on jsonb column postgresql9.5

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related