Full Text Search With PostgreSQL

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');

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:

• How is LIKE implemented?

• Pattern matching with LIKE, SIMILAR TO or regular expressions

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

StackExchange Database Administrators Q#33561, answer score: 9