How to create a TSVECTOR column in a materialized view? 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:


Adding a new column with a specific data type after the MV has been created.
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"
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 &amp; bar' @@ to_tsvector('english', title);

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