Thread: [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 stringinside tag column matches a given substring. What method should I use? The *Contains `@>` operator* will do full stringcomparisons as far as I understood. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Nov 8, 2017 02:34, "Arup Rakshit" <aruprakshit1987@outlook.com> 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.
The extension parray_gin (https://pgxn.org/dist/parray_gin/doc/parray_gin.html) offers the @@> operator.
Cheers,
Jeff
Re: [GENERAL] filter records by substring match of an postgresql array column
From
Arup Rakshit
Date:
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) ASu(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@outlook.com> 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 anystring inside tag column matches a given substring. What method should I use? The *Contains `@>` operator* will do fullstring comparisons as far as I understood. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, Nov 8, 2017 at 4:28 AM, Arup Rakshit <aruprakshit1987@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