Re: filter records by substring match of an postgresql array column

[Arup Rakshit <aruprakshit1987@xxxxxxxxxxx>]

I enabled the extension `pg_trgm`.

I thought to query like:

SELECT
   "videos".* 
FROM
   "videos" 
WHERE
   (
      array_to_string(tags, ', ') ilike '%web shorts%'
   )
   AND EXISTS
   (
      SELECT
      FROM
         unnest(tags) AS u(val) 
      WHERE
         u.val ILIKE '%web shorts%'
   )
;

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?



> On Nov 8, 2017, at 4:02 PM, Arup Rakshit <aruprakshit1987@xxxxxxxxxxx> wrote:
> 
> Hi,
> 
> I do have a videos table, and it has a column called `tags` of type array. I would like to select all videos where any string inside tag column matches a given substring. What method should I use? The *Contains `@>` operator* will do full string comparisons as far as I understood.



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general