On Wed, Nov 8, 2017 at 4:28 AM, Arup Rakshit <aruprakshit1...@outlook.com> wrote:
> > And to do I wanted to add an index like: > > CREATE INDEX trgm_idx_video_tags ON videos USING gist > ((array_to_string(tags, ', ')) gist_trgm_ops) > > But on running this I get an error as: > > ERROR: functions in index expression must be marked IMMUTABLE > > How can I fix this? > > wrap array_to_string with text[] argument into a function and mark it as immutable: create function txt_array_to_string (text[]) returns text language sql immutable as $$ select array_to_string($1,', ') $$; And then build your index on that function. I don't think there are any caveats on this. Array_to_string is not immutable because it can work with dates and numbers, which can change with configuration settings, such as timezone. But when given text[] argument, I think it actually is immutable. Cheers, Jeff
