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

On Wed, Nov 8, 2017 at 4:28 AM, Arup Rakshit 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

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

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%'

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

On Nov 8, 2017 02:34, "Arup Rakshit" 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 `@>`

[GENERAL] filter records by substring match of an postgresql array column

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. --