filter records by substring match of an postgresql array column

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

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

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