Skip to main content
34 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 ...
Erwin Brandstetter's user avatar
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 ...
Evan Carroll's user avatar
  • 63.7k
18 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 ...
Erwin Brandstetter's user avatar
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 ...
Erik Darling's user avatar
  • 41.9k
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 ...
Josh Darnell's user avatar
  • 29.4k
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. ...
Evan Carroll's user avatar
  • 63.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 ...
Randi Vertongen's user avatar
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:...
Erwin Brandstetter's user avatar
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 ...
Erwin Brandstetter's user avatar
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. ...
Evan Carroll's user avatar
  • 63.7k
9 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 ...
Avocado's user avatar
  • 223
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('...
Laurenz Albe's user avatar
  • 53.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. ...
Daniel Vérité's user avatar
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 ...
Michael's user avatar
  • 161
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 ...
Evan Carroll's user avatar
  • 63.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 ...
Evan Carroll's user avatar
  • 63.7k
7 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 ...
Neil McGuigan's user avatar
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 ...
Erik Darling's user avatar
  • 41.9k
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 ...
Erwin Brandstetter's user avatar
6 votes
Accepted

SQL Server Full-text crawl log location

You should be able to find the full text log location with the following code: xp_readerrorlog 0, 1, N'Logging SQL Server messages in file' I don't think it's possible to split them away from the ...
wBob's user avatar
  • 10.4k
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 ...
Solomon Rutzky's user avatar
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 ...
Evan Carroll's user avatar
  • 63.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 ...
Erwin Brandstetter's user avatar
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 ...
jjanes's user avatar
  • 40.2k
5 votes
Accepted

SQL Server: no search results caused by noise words

Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me. Use database; ALTER FULLTEXT INDEX ON table SET STOPLIST OFF; GO
Benedikt S. Vogler's user avatar
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....
McNets's user avatar
  • 23.8k
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, ...
Misha Brukman's user avatar
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) @@ ...
Ali Bagheri's user avatar
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 ...
Randi Vertongen's user avatar
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 ...
Randi Vertongen's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible