35
votes
Accepted
Get partial match from GIN indexed TSVECTOR column
Do all queries have to be in dictionary?
No. Because only word stems (according to the used text search configuration) are in the index to begin with. But more importantly:
No. Because, on top of ...
- 182k
24
votes
How can I use a full-text search on a jsonb column with Postgres?
PostgreSQL 10+
PostgreSQL 10 introduces Full Text Search on JSONB
CREATE INDEX ON table
USING gin ( to_tsvector('english',jsondata) );
The new FTS indexing on JSON works with phrase search and ...
- 64.7k
19
votes
Accepted
PostgreSQL full text search on many columns
I suggest this expression for query and index:
SELECT * FROM tbl
WHERE to_tsvector('simple', immutable_concat_ws(' ', country, city, street, house_nr, postcode))
@@ plainto_tsquery('simple', '22 ...
- 182k
17
votes
Accepted
Better approach for "LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE"
Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns.
Unless you're truly in need of indexing large ...
17
votes
Accepted
Full Text Search only performs 'starts with' searches
This is, I think, a common misconception when it comes to full text search in SQL Server.
Full Text Search enables searching for entire words as well as a number of other things. From the CONTAINS ...
- 29.8k
12
votes
Accepted
Full text search over multiple related tables: indices and performance
Optimization
You're going down the right track.
You either need to
Denormalize
Cache
Caching the results
What you probably want is a MATERIALIZED VIEW. This is easy and works reasonably well.
...
- 64.7k
12
votes
Accepted
Slow query when having 'contains' and '=' together in where clause
Your execution plan
When looking at the query plan, we can see that one index is touched to serve two filter operations.
Very simply put, due to the TOP operator, a row goal was set.
Much more ...
- 16.5k
12
votes
Accepted
Postgres full text search on words, not lexemes
Just use the simple text search configuration, which works with the simple dictionary and does not do any stemming:
CREATE INDEX ON tbl USING GIN (to_tsvector('simple', data));
And query accordingly:...
- 182k
10
votes
How can I use a full-text search on a jsonb column with Postgres?
PostgreSQL 11+
You can create a GiST or GIN index on jsonb columns with jsonb_to_tsvector('<language>', <jsonb_column>, <filter>) (or json columns with json_to_tsvector)
The filter ...
- 233
10
votes
Accepted
How to search hyphenated words in PostgreSQL full text search?
The key word here is phrase search, introduced with Postgres 9.6.
Use the "FOLLOWED BY" operator <-> or one of the related <N> operators. Or better yet, use the function ...
- 182k
9
votes
Accepted
Creating a case-insensitive and accent/diacritics insensitive search on a field
First, we need to clarify a few things.
Pattern Ops and Trigram
This type of query can work on varchar/text pattern_ops,
foo LIKE 'bar%'
Queries with Un-anchored search patterns reqire trigram.
...
- 64.7k
9
votes
Accepted
Multi language full text search using postgresql
You should definitely use a different column per language.
The main reason is that different languages have different stop words and stemming rules, so if you index something with to_tsvector('...
- 56.4k
8
votes
Full text search over multiple related tables: indices and performance
This looks like a fairly generic full-text search problem in a relational database.
Your prediction that updates in actors or objects would be troublesome in a denormalized structure looks spot-on.
...
- 32.3k
8
votes
Accepted
Postgres ts_headline inconsistent behaviour
I noticed my mistake.
The to_tsquery function inside also needs to know which dictionary to use (aka use the simple one). By default it uses english stemming which produces this result:
SELECT ...
- 161
8
votes
How to make middle and suffix matching using full text search?
The "Full" in Full Text Search means match full words, not parts of words
You should use RegExp or LIKE or tri-gram matching instead
You can mimic suffix matching by reversing words in your index ...
- 8,583
7
votes
UPDATE with join condition on matching words in columns of another table
The key here is that Column_1, represents three possible values for the JOIN. So what you want to use is string_to_array() (so long as those values are comma-separated and can not themselves include a ...
- 64.7k
7
votes
Accepted
How can I implement full-text search on a single value in a JSONB column?
The way I read this question, you only care about message. The difficulty here is that you need to,
map over a json array returning the message element
reduce/fold the array of message element ...
- 64.7k
7
votes
Accepted
Find records with same string with extra character
The SOUNDEX function can be applied to a column as well.
But since
there's thousands like that
I wouldn't suggest just writing a query to join on a function to do that.
This will likely not ...
6
votes
Finding the most commonly used non-stop words in a column
Your first query was pretty close. To remove the unwanted stemming, create a text search configuration with a simple dictionary that does not do it.
I suggest to use a separate schema for text search ...
- 182k
6
votes
SQL Server: Collation of the resource database
The database Collation for the first SELECT statement in my query is Latin1_General_CI_AS.
Well, not exactly. There are a few problems with this statement:
The Database's default Collation only ...
- 69.5k
6
votes
Accepted
PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()?
The difference between the string->tsvector cast and the function-constructor to_tsvector(text)
the cast (::) assumes the input is already a tsvector that's been casted to text. It does not use ...
- 64.7k
6
votes
Accepted
Postgres trigram match acting strange for specific characters
Other answers clarified that trigram similarity is based on alphanumeric characters only. That's why all your examples match 100 %.
You may still be able to make use of a trigram GiST or GIN index and ...
- 182k
6
votes
Accepted
When might a tsvector field pay for itself?
If you use proximity searching (like "phraseto_tsquery" for example), with a functional index it will have to re-parse each match candidate document to a tsvector and check it for the right sequence ...
- 41.3k
6
votes
Accepted
PostgreSQL unaccent and full text search for Arabic/Persian
Problem #1 is addressed by PostgreSQL 17, as mentioned in the release notes and the commit message:
Allow unaccent character translation rules to contain whitespace and
quotes (Michael Paquier)
The ...
- 32.3k
5
votes
UPDATE with join condition on matching words in columns of another table
Try this:
update table_b
set id_number = (select id_number
from table_a
where table_a.Column_1 like '%' || table_b.Column_1 || '%'
OR table_a....
- 23.9k
5
votes
Accepted
How we can do full text search and facets stuffs on BigQuery?
Is BigQuery not suitable for above purpose? Do we need to stick with any RDBMS database (e.g., PostgreSQL, MySQL, etc.) and search engine (e.g., Solr, Elasticsearch, etc.) for this purpose?
Correct, ...
- 208
5
votes
Accepted
How to use full text search in Persian using Postgresql?
use this.
select post_id
from comments
where to_tsvector('simple', comments.body) @@ to_tsquery('simple', 'کتاب') ;
OR
select post_id
from comments
where to_tsvector('simple', comments.body) @@ ...
- 166
5
votes
Accepted
QueryStore plan forcing limitations
When looking at the plan-forcing limitations one of the bullet points is:
Distributed query or full-text operations
However, this limitation does not mean that a delete should fail the plan ...
- 16.5k
5
votes
Why does CONTAINS not find matches at the end of the string?
This has to do with how stoplist (or noise words) is/are treated when adding the asterix (*) in combination with CONTAINS().
A longer explanation on that here.
In short: Default <> Prefix ...
- 16.5k
5
votes
Postgres trigram match acting strange for specific characters
SELECT show_trgm('$ Hello');
show_trgm
---------------------------------
{" h"," he",ell,hel,llo,"lo "}
(1 row)
SELECT show_trgm('- Hello');
show_trgm ...
- 56.4k
Only top scored, non community-wiki answers of a minimum length are eligible