Postgresql – How to create a TSVECTOR column in a materialized view

full-text-searchindexmaterialized-viewpostgresql

I am trying to implement full text search in a materialized view (MV for brevity), and I read that it's not possible to alter the MV itself, which rules out these solutions:

  1. Adding a new column with a specific data type after the MV has been created.
  2. Creating the MV with an empty column and change the column data type.

I tried both and well yes, as expected it doesn't work.

So my idea was to add a cast on the empty column when creating the MV:

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

But it returns the following error:

ERROR: syntax error at or near "::"

How can I achieve this?

ps: I am willing to have a column instead of an index because I'd like to use weighing.

Additional information

I am able to have the column created like so:

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

However I can't update the column using:

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

I get an error:

ERROR: cannot change materialized view "mv"

Best Answer

Why don't you drop the materialized view ,and recreate it in a transaction? You can't update a materialized view. They're a view that's been written to disk.

Instead of 

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,''))

Just do

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;

You don't have to have a materialized view for this though, yo can create a functional index which will work on the table directly

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

Or, just use the default cast..

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

And then depending, on your index condition,

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

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