postgresql - postgres中全文搜索的问题

Question

我有下一张表和数据:

/* script for people table, with field tsvector and gin */

CREATE TABLE public.people (
  id INTEGER,
  name VARCHAR(30),
  lastname VARCHAR(30),
  complete TSVECTOR
) 
WITH (oids = false);

CREATE INDEX idx_complete ON public.people
  USING gin (complete);

/* data for people table */

INSERT INTO public.people ("id", "name", "lastname", "complete")
VALUES 
  (1, 'MICHAEL', 'BRYANT BRYANT', '''bryant'':2,3 ''michael'':1'),
  (2, 'HENRY STEVEN', 'BUSH TIESSEN', '''bush'':3 ''henri'':1 ''steven'':2 ''tiessen'':4'),
  (3, 'WILLINGTON STEVEN', 'STEPHENS FLINN', '''flinn'':4 ''stephen'':3 ''steven'':2 ''willington'':1'),
  (4, 'BRET', 'MARTINEZ AROCH', '''aroch'':3 ''bret'':1 ''martinez'':2'),
  (5, 'TERENCE BERT', 'CAVALIERE ENRON', '''bert'':2 ''cavalier'':3 ''terenc'':1');

我需要根据 tsvector 字段检索名字和姓氏。其实我有疑问:

SELECT * FROM people WHERE complete @@ to_tsquery('WILLINGTON & FLINN');

而且结果是对的(第三条记录)。但是如果我尝试

SELECT * FROM people WHERE complete @@ to_tsquery('STEVEN & FLINN');
/* the same record! */

我没有结果。为什么？我能做什么？

Answer 1

您应该使用与插入字段“完整”中的值相同的语言来搜索表格。

比较英语和德语的查询结果:

select * ,
to_tsvector('english', concat_ws(' ', name, lastname )) as english,
to_tsvector('german', concat_ws(' ', name, lastname )) as german
from public.people 

所以这对你有用:

SELECT * FROM people WHERE complete @@ to_tsquery('english','STEVEN & FLINN');