3

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"

  • 'doesn't work' is not really a description that explains your problem. How exactly it doesn't work? – dezso Sep 12 '17 at 13:56
  • @dezso Edited question. – Stanislasdrg Sep 12 '17 at 14:03
  • 1
    Why don't you drop the materialized view and recreate it in a transaction? – Evan Carroll Sep 12 '17 at 14:50
3

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;
  • Thanks, for your help. I can drop the MV without problem and recreate it. However I can't update a column based index. If I go with the regular GIN index, I am afraid there isn't a way to apply weights to columns. Am I right ? – Stanislasdrg Sep 12 '17 at 15:14
  • @Stanislasdrg you can apply weights, but you need a unique index for every possible combination of weights. USING GIN ( SETWEIGHT(to_tsvector())||SETWEIGHT(to_tsvector())||SETWEIGHT(to_tsvector())) then be sure to use that same condition in your query. – Evan Carroll Sep 12 '17 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.