Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i have a table with this rows:

Stickers
------------------------------------------------------
ID | Title                 |Keywords (ts_vector)
------------------------------------------------------
01 | Sticker Case 580H     |'580h':3 'cas':2 'stick':1
02 | Sticker Case 580L     |'580l':3 'cas':2 'stick':1
03 | Sticker Case 580      |'580':3 'cas':2 'stick':1
04 | Sticker Case Plus 3000|'3000':4 'cas':2 'plus':3 'stick':1

Well, when i do search using this script, just row 03 return, how i do return the row 01 and 02?

SELECT
*
FROM
stickers
WHERE
keywords @@@ to_tsquery('case & 580');
share|improve this question
1  
You can use : to_tsquery('case & 580*') Note the asterisk after 580. –  Mark Selby Apr 21 '13 at 11:12
    
We implement the Apache Solr for this situations. –  Tomazio Jul 30 at 19:07

3 Answers 3

up vote 6 down vote accepted

This is not really a use case for full text search because full text relies on stemming the text and parsing the chunks into tokens. As you can see from keywords, '580h' is parsed as its own word because there's no language in which '580' is a "stem" of '580h'. You'd probably be better off with regular expression matching.

Here's a query that I worked up for you:

SELECT id, title 
  FROM stickers WHERE
    (title ~* '580')
      AND
    (title ~* 'case')
ORDER BY id
share|improve this answer
SELECT
*
FROM
stickers
WHERE
keywords @@ to_tsquery('case & 580:*')

will work.

Postgres text search allows for prefix searching, which is represented by the :* in the query. It will match any token starting with 580

share|improve this answer
    
Some explanation could probably repel downvotes. –  dezso Jul 30 at 8:36

In addition to what @swasheck already explained, you'll probably get better performance with LIKE (~~) and ILIKE (~~*) in combination with a trigram GiST or GIN index. You'll have to install the additional module pg_trgm for that. Find details under these related questions:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Trigram indexes can be used for basic regular expression matches (~) as well, but LIKE is generally faster.

Create an index like:

CREATE INDEX stickers_title_gin_trgm_idx ON stickers
USING gin (title gin_trgm_ops);

Then this should be very fast:

SELECT *
FROM   stickers
WHERE  title ~~* '%case 580%';

Or (not clear from your question):

SELECT *
FROM   stickers
WHERE  title ~~* '%case%'
AND    title ~~  '%580%';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.