5

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');
2
  • 1
    You can use : to_tsquery('case & 580*') Note the asterisk after 580.
    – Mark Selby
    Apr 21, 2013 at 11:12
  • We implement the Apache Solr for this situations.
    – Tom
    Jul 30, 2014 at 19:07

3 Answers 3

6

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
9

In addition to what swasheck 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. See:

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%';
2
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

1
  • Some explanation could probably repel downvotes. Jul 30, 2014 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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