<p dir="auto"><strong>Migrated issue, originally created by Priit Laes (<a href="https://github.com/plaes">@plaes</a>)</strong></p>
<p dir="auto">Postgres full text search with query strings that are not in proper format fails. Example from documentation:</p>
<p dir="auto">Following SQLAlchemy code (taken from "Full Text Search" section under PostgreSQL dialect docs):</p>
<p dir="auto"><code class="notranslate">select([sometable.c.text.match("search string")])</code><br>
should emit following SQL:</p>
<p dir="auto"><code class="notranslate">SELECT text @@ to_tsquery('search string') FROM table</code></p>
<p dir="auto">But when executing that (using real column name and table name) ends up with:</p>
<p dir="auto"><code class="notranslate">ERROR:  syntax error in tsquery: "search string"</code><br>
There's <code class="notranslate">plainto_tsquery</code> function that should be used instead, when query string is not in proper format:<br>
<code class="notranslate">SELECT text @@ plainto_tsquery('search string') FROM table</code></p>
<p dir="auto">My idea would be to use plainto_tsquery with column.match and introduce column.match_tsquery which ends up emitting to_tsquery SQL.</p>
<p dir="auto"><strong>Migrated issue, originally created by jvanasco (<a href="https://github.com/jvanasco">@jvanasco</a>)</strong></p>
<p dir="auto">I recently realized some implementation details of <code class="notranslate">to_tsquery</code> that are incompatible with the docs I drafted and how sqlalchemy integrates it.</p>
<p dir="auto">Not sure how to handle this.</p>
<p dir="auto">I based the docs/examples on existing tests and text.  So we have this as the first bit of "Full Text Search"  ( <a href="http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#full-text-search" rel="nofollow">http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#full-text-search</a> )</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select([sometable.c.text.match(&quot;search string&quot;)])
SELECT text @@ to_tsquery('search string') FROM table"><pre class="notranslate"><code class="notranslate">select([sometable.c.text.match("search string")])
SELECT text @@ to_tsquery('search string') FROM table
</code></pre></div>
<p dir="auto">well, if we put this into psql...</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="badsql=&gt; select to_tsquery('search string') ;
ERROR:  syntax error in tsquery: &quot;search string&quot;"><pre class="notranslate"><code class="notranslate">badsql=&gt; select to_tsquery('search string') ;
ERROR:  syntax error in tsquery: "search string"
</code></pre></div>
<p dir="auto">that's because tsquery has a rigid enforcement of input.  text must either be tokenized and joined with acceptable operators :</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select to_tsquery('cat &amp; rat');"><pre class="notranslate"><code class="notranslate">select to_tsquery('cat &amp; rat');
</code></pre></div>
<p dir="auto">be quoted :</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select to_tsquery('''search string''');"><pre class="notranslate"><code class="notranslate">select to_tsquery('''search string''');
</code></pre></div>
<p dir="auto">or use the alternate function</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select plainto_tsquery('search string');"><pre class="notranslate"><code class="notranslate">select plainto_tsquery('search string');
</code></pre></div>
<p dir="auto">So, these are acceptable:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select to_tsquery('search &amp; string');
select to_tsquery('''search string''');
select plainto_tsquery('search string');"><pre class="notranslate"><code class="notranslate">select to_tsquery('search &amp; string');
select to_tsquery('''search string''');
select plainto_tsquery('search string');
</code></pre></div>
<p dir="auto">but this is not:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="select to_tsquery('search string');"><pre class="notranslate"><code class="notranslate">select to_tsquery('search string');
</code></pre></div>
<p dir="auto">I'm not sure the best way to handle this nuance in the docs.</p>
<p dir="auto">As far as the implementation detail goes...</p>
<p dir="auto">this creates issues with <code class="notranslate">Column.match</code>, because that generates (invalid) sql like this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="table.column @@ to_tsquery('search string')"><pre class="notranslate"><code class="notranslate">table.column @@ to_tsquery('search string')
</code></pre></div>
<p dir="auto">not valid sql  like:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="table.column @@ to_tsquery('''search string''')
table.column @@ plainto_tsquery('search string')"><pre class="notranslate"><code class="notranslate">table.column @@ to_tsquery('''search string''')
table.column @@ plainto_tsquery('search string')
</code></pre></div>
<p dir="auto">This is an annoying implementation detail , but the content/type of string will affect the function or format t search on.</p>
<p dir="auto">i thought about just regexing this into submission, but the multitude of possible operators and edge cases suggests that the text would need to be parsed for tokenization instead -- otherwise throwing in a bit of text that has an operator will function as a real operator and probably trigger an invalid syntax.</p>
<p dir="auto">i think the easiest scenario would be to replace match's <code class="notranslate">to_tsquery</code> with <code class="notranslate">plainto_tsquery</code></p>