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 <aruprakshit1...@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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
