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.
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"