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');
postgresql full-text-search postgresql-9.2
asked Jan 25 '13 at 16:49
TommyTommy
3315
add a comment |
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');
postgresql full-text-search postgresql-9.2
asked Jan 25 '13 at 16:49
TommyTommy
3315
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.
– Tom
Jul 30 '14 at 19:07
add a comment |
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');
postgresql full-text-search postgresql-9.2
asked Jan 25 '13 at 16:49
TommyTommy
3315
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');
postgresql full-text-search postgresql-9.2
postgresql full-text-search postgresql-9.2
asked Jan 25 '13 at 16:49
TommyTommy
3315
asked Jan 25 '13 at 16:49
TommyTommy
3315
asked Jan 25 '13 at 16:49
TommyTommy
3315
asked Jan 25 '13 at 16:49
TommyTommy
3315
asked Jan 25 '13 at 16:49
TommyTommy
3315
3315
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.
– Tom
Jul 30 '14 at 19:07
add a comment |
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.
– Tom
Jul 30 '14 at 19:07
1
1
You can use : to_tsquery('case & 580*') Note the asterisk after 580.
– Mark Selby
Apr 21 '13 at 11:12
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.
– Tom
Jul 30 '14 at 19:07
We implement the Apache Solr for this situations.
– Tom
Jul 30 '14 at 19:07
add a comment |
3 Answers
3
active
oldest
votes
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
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
add a comment |
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%';
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
add a comment |
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
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f33561%2ffull-text-search-with-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
add a comment |
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
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
add a comment |
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
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
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
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
edited Jan 25 '13 at 18:30
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
answered Jan 25 '13 at 18:20
swasheckswasheck
7,66333882
7,66333882
add a comment |
add a comment |
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%';
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
add a comment |
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%';
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
add a comment |
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%';
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
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%';
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
edited 1 hour ago
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
answered Jan 26 '13 at 14:37
Erwin BrandstetterErwin Brandstetter
91k9170283
91k9170283
add a comment |
add a comment |
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
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
add a comment |
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
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
add a comment |
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
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
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
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
edited Jul 30 '14 at 15:33
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
answered Jul 29 '14 at 18:56
Neil McGuiganNeil McGuigan
5,47932344
5,47932344
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
add a comment |
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
Some explanation could probably repel downvotes.
– dezso
Jul 30 '14 at 8:36
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f33561%2ffull-text-search-with-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.
– Tom
Jul 30 '14 at 19:07