CODEWITHSUNDEEP
postgresqlindexingjsonb
CitizenFish
CitizenFish

Reputation: 391

jsonb text search index

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

Upvotes: 1

Views: 368

Answers (1)

Jeremy
Jeremy

Reputation: 6723

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));

Upvotes: 1

Related Questions