Обсуждение: FTS question

I created a FTS index:
CREATE INDEX clients_fts ON clients USING gist( to_tsvector( 'english', name) )

I can do FTS queries like:
SELECT * FROM clients
WHERE to_tsvector('english',name) @@ to_tsquery( 'english','somestring');

But if I want to query just part of the string return set is empty:
SELECT * FROM clients
WHERE to_tsvector('english',name) @@ to_tsquery( 'english','somes');

returns no results.

Is it that postgresql doesn't support substring queries or have I missed something. I can not find anything on the net.
Postgresqlis v8.3.1. 


by
TheR

On Mon, 30 Jun 2008, Damjan Rems wrote:

> I created a FTS index:
> CREATE INDEX clients_fts ON clients USING gist( to_tsvector( 'english', name) )
>
> I can do FTS queries like:
> SELECT * FROM clients
> WHERE to_tsvector('english',name) @@ to_tsquery( 'english','somestring');
>
> But if I want to query just part of the string return set is empty:
> SELECT * FROM clients
> WHERE to_tsvector('english',name) @@ to_tsquery( 'english','somes');
>
> returns no results.
>
> Is it that postgresql doesn't support substring queries or have I missed something. I can not find anything on the
net.Postgresql is v8.3.1. 
>

prefix search support will be supported in 8.4.

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Damjan Rems wrote:
> SELECT * FROM clients
> WHERE to_tsvector('english',name) @@ to_tsquery( 'english','somestring');
>
> But if I want to query just part of the string return set is empty:
> SELECT * FROM clients
> WHERE to_tsvector('english',name) @@ to_tsquery( 'english','somes');
>
> returns no results.
>
> Is it that postgresql doesn't support substring queries or have I missed something. I can not find anything on the
net.Postgresql is v8.3.1. 

Um - you're using the full-text indexing. It's supposed to search words.

You can use any of the pattern-matching functions / operators (LIKE etc)
here if you want to explicitly match characters.
http://www.postgresql.org/docs/current/static/functions-matching.html

If that's not what you're after, you'll need to explain what you are
trying to do.

--
   Richard Huxton
   Archonet Ltd

> Um - you're using the full-text indexing. It's
> supposed to search words.
>
> You can use any of the pattern-matching functions /
> operators (LIKE etc)
> here if you want to explicitly match characters.
> http://www.postgresql.org/docs/current/static/functions-matching.html
>
> If that's not what you're after, you'll need to
> explain what you are
> trying to do.

Id would like to get aproximatly this.
WHERE name LIKE 'somes%'

But how do I construct FTS query for this?

by
TheR

Damjan Rems wrote:
>> Um - you're using the full-text indexing. It's
>> supposed to search words.
>>
>> You can use any of the pattern-matching functions /
>> operators (LIKE etc)
>> here if you want to explicitly match characters.
>> http://www.postgresql.org/docs/current/static/functions-matching.html
>>
>> If that's not what you're after, you'll need to
>> explain what you are
>> trying to do.
>
> Id would like to get aproximatly this.
> WHERE name LIKE 'somes%'
>
> But how do I construct FTS query for this?

Well, you don't. Like Oleg says, there should be prefix support in 8.4,
but even then I'm not sure It will do precisely what you want.

Depending on the various transformations / stopwords you have set up in
your dictionaries a single search-word might end up as one or several.

What precisely are you trying to do? I'm having difficulties figuring
out when I'd want "cat" to match "catch" and "catamaran" at the same time.

--
   Richard Huxton
   Archonet Ltd

On Tue, Jul 01, 2008 at 01:14:12AM -0700, Damjan Rems wrote:
> Id would like to get aproximatly this.
> WHERE name LIKE 'somes%'
> But how do I construct FTS query for this?

for this - you dont need ftps. you just need proper index.

there will be prefix searches in full text indexes in postgresql 8.4:
http://www.depesz.com/index.php/2008/05/17/waiting-for-84-partial-match-support-in-gin-and-sequence-restart/

in the mean time you might want to check:
http://www.depesz.com/index.php/2007/09/15/speeding-up-like-xxx/

but if you really always have things like "name like 'somes%' - ie.
column like 'prefix-search%'; then all you need is simple btree index on
name column.

Best regards,

depesz