我正在嘗試為可能包含幾種不同語言之一的文本的欄位創建全文搜尋的 GIN 索引。語言是預設的，我們會在查詢時知道要搜尋的語言。這是我到目前為止的架構……

CREATE SCHEMA source;
CREATE SCHEMA common;

CREATE TABLE common.lang (
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL UNIQUE,
   code TEXT NOT NULL UNIQUE,
   created_at TIMESTAMP NOT NULL DEFAULT now()
);

INSERT INTO common.lang (name, code) VALUES
 ('english', 'en'),
 ('arabic', 'ar'),
 ('chinese', 'zh'),
 ('undefined', 'und');

CREATE TABLE source.user (
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL,
   screen_name TEXT NOT NULL UNIQUE,
   profile_lang_id INTEGER NOT NULL REFERENCES common.lang(id) ON DELETE CASCADE ON UPDATE CASCADE,
   created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX source_user_names_idx ON source.user USING GIN (to_tsvector(SELECT common.lang.name FROM source.user INNER JOIN common.lang ON common.lang.id = source.user.profile_lang_id, name || ' ' || screen_name));

它在“SELECT”處或附近報告語法錯誤，這是有道理的……我不認為我可以在那裡硬塞一個 select 語句，但我不確定如何在 to_tsvector 呼叫中正確使用 select 語句.

我查看了https://stackoverflow.com/a/21299033/4471711，但是這對語言進行了硬編碼，而我希望在創建索引時動態地從表中提取語言。顯然，如果/當我們添加一種語言時，我們需要重新創建索引。

我還查看了https://stackoverflow.com/a/15135730/4471711，但是這不支持創建索引，只需要 to_tsvector 的 1 個參數，而我似乎需要同時傳遞語言和文本柱子。

我還嘗試在更多文件樣式的欄位上創建全文搜尋索引，如下所示：

CREATE TABLE source.post (
   id BIGSERIAL PRIMARY KEY,
   text TEXT NOT NULL
);

CREATE INDEX status_text_idx ON source.post USING GIN (to_tsvector(common_lang_id, text));

同樣，對於上面的索引，我還想使用由外鍵指定的語言到 lang 表。

我的兩個問題是：

1. 如何正確使用 SELECT 語句使用使用者表中的外鍵從 lang 表中獲取語言？
2. 如果 lang 作為選項之一是“未定義的”怎麼辦？我不介意為了 to_tsvector 呼叫而預設使用英語…如何添加該預設值，同時仍然允許使用者表引用“未定義”？

1. 你沒有。您不能創建引用另一個表的索引。
2. 要設置預設值，請參閱default-text-search-config

解決方法

但是，您可以在索引中使用函式。並且函式可以引用外部表。也就是說，使用這種方法是一種 hack，因為更改表 ( common.lang) 將需要重新索引並清除會話記憶體。

CREATE FUNCTION common.mylookup(id int)
RETURNS regconfig AS $$
 SELECT name::regconfig
 FROM common.lang
 WHERE id = id
$$
LANGUAGE sql
IMMUTABLE;

CREATE INDEX
 ON source.user
 USING GIN (to_tsvector(common.mylookup(profile_lang_id), name || ' ' || screen_name ));

您可以將功能標記為IMMUTABLE允許這樣做。如果基礎表發生突變，您將不得不REINDEX索引。在使用這種 hack 的項目中，比如 PostGIS，他們在點發布上重新創建索引。

跟進

@EvanCarroll，我正在嘗試在名稱上創建 tsvector || ’ ’ || 螢幕名稱。– 布魯克斯 4 分鐘前

全文搜尋不能做你認為它做的事情。它不在那裡搜尋多個欄位。它可以對單詞內容進行矢量化，並利用字典、存根、詞法分析器、地名詞典、停用詞消除以及許多其他不適用的技巧。如果這對您沒有意義，您將不得不閱讀文件。如果您想要的是 grep，那麼 FTS 很少是您想要的。如果您想對小塊非標准文本（如名稱）進行 grep，這不是您想要的。您可能想要三元組索引。

如果您想要的只是%term%兩個欄位上的一個，那麼您最好只使用trigram index來做到這一點。

CREATE EXTENSION pg_trgm;
CREATE INDEX ON source.user USING GIN ((name || ' ' || screen_name) gin_trgm_ops);
WHERE name || ' ' || screen_name like '%$1%';

甚至更好，

CREATE INDEX ON source.user USING GIN (name gin_trgm_ops, screen_name gin_trgm_ops);
WHERE name LIKE '%$1%' OR screen_name LIKE '%$1%';

引用自：https://dba.stackexchange.com/questions/182510