如何使用array_to_string索引Postgresql中的varchar数组？ [英] How to index a varchar array in Postgresql using array_to_string?

问题描述

我想在postgresql 9.3中的 varchar 数组列中设置索引。有人告诉我使用 array_to_string（col）设置它，但我真的不明白这是如何工作的。我想出了以下声明：

I'm looking to set an index in a varchar array column in postgresql 9.3. I was told to set it using array_to_string(col) but I don't really understand how this works. I came up with the following statement:

CREATE INDEX CONCURRENTLY rtb_id_search ON sites USING GIN(array_to_string(rtb_id, ''));

然而，postgresql抱怨：

However, postgresql complains with:

 ERROR:  functions in index expression must be marked IMMUTABLE

推荐答案

create function string_array_to_string(text[], text, text) returns text as $$
   select array_to_string($1, $2, $3)
$$ language sql cost 1 immutable;

create index concurrently sites_rtb_ids on sites using gin (string_array_to_string(rtb_ids, ' ', ' ') gin_trgm_ops);

这是创建索引的方法。使用的函数需要标记为不可变。

This is the way to create the index. The function used needs to be marked immutable.

这篇关于如何使用array_to_string索引Postgresql中的varchar数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！