Stack Overflow
Stack Overflow

Questions tagged [pg-trgm]

Postgres trigram match module

Postgres trigram match module https://www.postgresql.org/docs/current/static/pgtrgm.html

58 questions
2
votes
2 answers

Will trigram index on multiple columns make search faster and how to make such search properly?

Let's assume I have table with multiple columns. For instance: id int name text surname text cars json example record would be +----+------+---------+------------------------------------+ | id | name | surname | cars …
Sheyko Dmitriy
  • 383
  • 1
  • 3
  • 15
2
votes
0 answers

Django Postgres search with TrigramSimilarity

If I use 'title' in my post_search function it works perfectly. Post.objects.annotate( similarity=TrigramSimilarity('title', query) …
farschmessivo
  • 49
  • 8
2
votes
0 answers

Find fuzzy duplicates on single column rows with pg_trgm

I was trying to find duplicates on column rows, but as they are fuzzy (not the same value, misspelling, indent space) I have to use pg_trgm extensions and similarity() function to find those. The problem is: this query is quite long and inefficent,…
Pavel Nasevich
  • 400
  • 1
  • 3
  • 13
2
votes
2 answers

How to use pg_trgm operators(e.g. %>) in django framework?

I'm using the pg_trgm for similarity search on PostgreSQL DB and I need to return the results to the front by using the Django model. But, I got a problem that the operator %> cannot be recognized by the Django framework. Any advice? Thank you. I…
Chasedream
  • 23
  • 3
2
votes
0 answers

Postgres 9.5 cache lookup failed

I am trying to install pg_trgm into postgres (Using postgres 9.5 on ubuntu 16) by doing CREATE EXTENSION pg_trgm. The first executable line of pg_trgm--1.1.sql is CREATE FUNCTION set_limit(float4) RETURNS float4 AS 'MODULEPATH_NAME' LANGUAGE C…
manji369
  • 186
  • 4
  • 16
2
votes
1 answer

postgresql pg_trgm.word_similarity_threshold decreasing

SET pg_trgm.word_similarity_threshold TO 0.2; lowers the threshold for current session but does not do it for database. I need to lower the threshold for supporting spelling mistakes.
Rakesh
  • 21
  • 5
2
votes
1 answer

Adding trigram index to Postgres table in Rails 5

Postgres allows for trigram indices with the pg_trgm module. Here's the example code they provide in the "Index Support" section: CREATE TABLE test_trgm (t text); CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops); Here is the…
Powers
  • 18,150
  • 10
  • 103
  • 108
2
votes
1 answer

How to speed up pg_trgm?

Create table: CREATE TABLE public.personal ( id bigserial NOT NULL PRIMARY KEY, first_name character varying(255) NOT NULL, last_name character varying(255), middle_name character varying(255), deleted integer NOT…
Aleksandr Maybach
  • 610
  • 8
  • 20
2
votes
1 answer

What is the syntax for AND OR NOT in Postgres trigram search?

I have implemented Postgres 9.6 trigram search https://www.postgresql.org/docs/9.6/static/pgtrgm.html into my application which works fine for a single search term. I can't see how to allow my users to do AND OR NOT searches though. Currently, if I…
Duke Dougal
  • 24,359
  • 31
  • 91
  • 123
2
votes
0 answers

Regex pattern matching with pg_trgm (trigram matching)

I have a database in postgresql called mydata with a field called text. I'm interested in doing regex pattern matching and only returning the snippet of the match, not the entire text. I know you can use pg_trgm (creates a trigram matching index) to…
Rich
  • 785
  • 1
  • 9
  • 22
1
vote
2 answers

How to index a column for leading wildcard search and check progress?

My table has 650M rows (according to a fast but decently precise estimate from a query I found here). It has a text column called receiver_account_id, and I need to be able to search those records like: r.receiver_account_id LIKE…
Ryan
  • 22,332
  • 31
  • 176
  • 357
1
vote
1 answer

Implementing K-Gram indexing in postgres

I am trying to implement an index in postgres for wild card queries such as SELECT * FROM TABLENAME WHERE COL1 LIKE '%'; I know postgres offers gin and gist trigram indexing through pg_trgm extension. But, it stores the trigrams of the text…
Ashwin Prasad
  • 108
  • 1
  • 6
1
vote
1 answer

How to search many values with Postgres trigram?

If I have a table with the values name and surname, how do I search for those two values? Example I set a threshold to filter out unwanted values, and then I can find similarity with table name and searched text; however, if there is only name or…
Dmiich
  • 325
  • 2
  • 16
1
vote
0 answers

how to use pg_trgm operators e.g (<-> ) in python

I'm using the pg_trgm for similarity search on PostgreSQL DB and I need to return the results to the PostGIS table, but I'm getting this programmer error, I learned that this error is related to syntax of the sql query I tried same query in…
Kerim Mirzeyev
  • 23
  • 2
1
vote
0 answers

Strange sorting behavior with bigint column via GiST index in PostgreSQL

I'm working on implementing a fast text search in PostgreSQL 12.6 (Ubuntu 20.04.2 VBox) with custom sorting, and I'm using pg_trgm along with GiST (btree_gist) index for sorted output. The idea is to return top 5 matching artists that have the…
DrazenG
  • 11
  • 2
1
2
3 4