Syntaxerror, when creating an MATERIALIZED VIEW of tsvectors for fulltextsearch in postgresql

Question

I am trying to implement a full text search, taking spelling mistakes into account. Therefor, I try to create a MATERIALIZED VIEW of tsvector of all relevant columns.

CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat(
'SELECT to_tsvector('simple', cve.descriptions) || 
    to_tsvector('simple', cpeMatch.criteria) ||
    to_tsvector('simple', array_to_string(reference.tags, ' '))
FROM cve
JOIN cpeMatch ON cpeMatch.cve_id = cve.id
JOIN reference ON reference.cve_id = cve.id
GROUP BY cve.id');

But when I run this code, I get:

SQL-Fehler [42601]: FEHLER: Syntaxfehler bei »simple«
  Position: 92

Saying there is a syntax error at 'simple'. I have no idea how to resolve this issue. Just to make clear, I installed pg_trgm but didn't make any configs ore changes.

score 3 · Accepted Answer · answered Jan 13 '23 at 16:22

You need to quote simple but you are already in a quoted string. The easiest is to change the string delimiter:

CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat(
$$SELECT to_tsvector('simple', cve.descriptions) || 
    to_tsvector('simple', cpeMatch.criteria) ||
    to_tsvector('simple', array_to_string(reference.tags, ' '))
FROM cve
JOIN cpeMatch ON cpeMatch.cve_id = cve.id
JOIN reference ON reference.cve_id = cve.id
GROUP BY cve.id$$);

Syntaxerror, when creating an MATERIALIZED VIEW of tsvectors for fulltextsearch in postgresql

1 Answers1