jsonb text search index

Question

I have address data split into fields in a JSONB column

I want to run a free text search over this data and noted that postgres 11 has the function jsonb_to_tsvector()

I am trying to create an index on my table with:-

CREATE INDEX ad_jsonb_ts_vector ON my_address_data USING GIN (jsonb_to_tsvector('English'::regconfig, address_data::JSONB, jsonb_build_array('text', 'numeric')));

This gives me the error:-

ERROR: functions in index expression must be marked IMMUTABLE SQL state: 42P17

But as I understand it this form of jsonb_to_tsvector IS immutable?

select provolatile from pg_proc where proname = 'jsonb_to_tsvector' AND oid::regprocedure ='jsonb_to_tsvector(regconfig,jsonb,jsonb)'::regprocedure

Returns "i".

Have I missed something or is this a postgres bug. Version is 11.5

Jeremy · Accepted Answer · 2020-01-20 12:09:01Z

1

The function jsonb_build_array is stable, not immutable. Try this instead:

CREATE INDEX ad_jsonb_ts_vector ON my_address_data USING GIN (jsonb_to_tsvector('English'::regconfig, address_data::JSONB, '["text", "numeric"]'::jsonb));

answered Jan 20, 2020 at 12:09

Jeremy

6,77322 silver badges21 bronze badges

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

1 Comment

CitizenFish Over a year ago

Thanks, it seemed odd that jsonb_build_array is not immutable. Further reading shows that it is linked to the output which can change via other indirectly invoked functions.

Collectives™ on Stack Overflow

jsonb text search index

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related