如何在物化视图中创建TSVECTOR列？

Question

我试图在物化视图(MV表示简洁)中实现全文搜索，并且我读到不可能更改MV本身，这排除了以下解决方案：

1. 在创建MV之后添加具有特定数据类型的新列。
2. 使用空列创建MV并更改列数据类型。

我两次都试过了，是的，就像预期的那样，它不起作用。

因此，我的想法是在创建MV时在空列上添加一个强制转换：

CREATE MATERIALIZED VIEW mv AS 
  SELECT id, '' AS textsearchable_index_col::tsvector
  FROM tbl
  ...
WITH NO DATA

但是它返回以下错误：

ERROR: syntax error at or near "::"

我怎样才能做到这一点？

ps:我愿意用一列而不是索引，因为我想用称重。

附加信息

我可以这样创建该列：

 CREATE MATERIALIZED VIEW mv AS 
   SELECT id, CAST ('' AS tsvector) AS textsearchable_index_col
   FROM tbl
   ...
 WITH NO DATA

但是，我无法使用以下方法更新该列：

UPDATE mv SET textsearchable_index_col = to_tsvector('english', coalesce(title,''))

我收到一个错误：

ERROR: cannot change materialized view "mv"

Answer 1

为什么不删除物化视图，并在事务中重新创建它呢？无法更新物化视图。它们是被写入磁盘的视图。

而不是

CREATE MATERIALIZED VIEW mv AS 
   SELECT id, CAST ('' AS tsvector) AS textsearchable_index_col
   FROM tbl
[...]
UPDATE mv SET textsearchable_index_col = to_tsvector('english', coalesce(title,''))

就这么做

BEGIN;
  DROP MATERIALIZED VIEW IF EXISTS mv;
  CREATE MATERIALIZED VIEW mv AS 
     SELECT
       id,
       textsearchable_index_col = to_tsvector('english', coalesce(title,''))
     FROM tbl
COMMIT;

不过，您不必为此创建一个物化视图，您可以创建一个功能索引，它将直接在表上工作。

CREATE INDEX ON tbl USING GIN (to_tsvector('english', title));

或者，只需使用默认的转换..。

CREATE INDEX ON tbl USING GIN ((title::tsvector));

然后根据你的索引条件，

SELECT * FROM tbl
WHERE 'foo & bar' @@ to_tsvector('english', title);

SELECT * FROM tbl
WHERE 'foo & bar' @@ title::tsvector;