How to get postgres plainto_tsquery, but with stems separated by OR (|) instead of AND (&)?

huangapple go评论62阅读模式
英文:

How to get postgres plainto_tsquery, but with stems separated by OR (|) instead of AND (&)?

问题

以下是在Postgres 15.1中的一个plainto_tsquery调用示例:

# select plainto_tsquery('english', 'postgres is the best');
  plainto_tsquery  
-------------------
 'postgr' & 'best'
(1 row)

这等同于

select to_tsquery('english', 'postgr & best');
    to_tsquery     
-------------------
 'postgr' & 'best'

文档中提到

plainto_tsquery将未格式化的文本querytext转换为tsquery值。文本被解析和规范化,类似于to_tsvector,然后在幸存的单词之间插入&(AND)tsquery运算符。

我如何从文本短语中获得相同的结果(已解析和规范化),但在标记之间使用|,例如等同于

# select to_tsquery('english', 'postgr | best');
    to_tsquery     
-------------------
 'postgr' | 'best'

但能够将"postgres is the best"作为输入传递?

英文:

Here is an example plainto_tsquery call in postgres 15.1:

# select plainto_tsquery('english', 'postgres is the best');
  plainto_tsquery  
-------------------
 'postgr' & 'best'
(1 row)

This is the equivalent of

select to_tsquery('english', 'postgr & best');
    to_tsquery     
-------------------
 'postgr' & 'best'

The docs say

> plainto_tsquery transforms the unformatted text querytext to a tsquery value. The text is parsed and normalized much as for to_tsvector, then the & (AND) tsquery operator is inserted between surviving words.

How do I get the same result (parsed and normalized) from a text phrase, but with '|' between the tokens, e.g., the equivalent of

# select to_tsquery('english', 'postgr | best');
    to_tsquery     
-------------------
 'postgr' | 'best'

but being able to pass in "postgres is the best" as input?

答案1

得分: 1

I don't know a direct way, but that many conversions isn't good for performance.

Also it is possible to make a string and then make a tsquery from it instead of a String.

SELECT string_agg(quote_literal(lexeme)::text, ' | ') 
  FROM  unnest((select   to_tsvector ('english', 'postgres is the best')));
英文:

I don't know a direct way, but that many conversions isn't good for performance.

Also it is possible to make a string and then make a tsquery from it instead of a String.

SELECT string_agg(quote_literal(lexeme)::text, ' | ') 
  FROM  unnest((select   to_tsvector ('english', 'postgres is the best')));
string_agg
'best' | 'postgr'
SELECT string_agg(lexeme::text, ' | ')::tsquery 
  FROM  unnest((select   to_tsvector ('english', 'postgres is the best')));
string_agg
'best' | 'postgr'

fiddle

答案2

得分: 1

使用 websearch_to_tsquery():

select websearch_to_tsquery('english', 'postgres or best');

 websearch_to_tsquery 
══════════════════════
 'postgr' | 'best'
(1 )
英文:

Use websearch_to_tsquery():

select websearch_to_tsquery('english', 'postgres or best');

 websearch_to_tsquery 
══════════════════════
 'postgr' | 'best'
(1 row)

huangapple
  • 本文由 发表于 2023年7月7日 06:43:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76632935.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定