Mfcttrf

What adjective means "accurately representitive of reality"?

How quality assurance engineers test calculations?

When I press the space bar it deletes the letters in front of it

Chorophyll and photosynthesis in plants with coloured leaves

What is the minimum time required for final wash in film development?

Is there a nice way to implement a conditional type with default fail case?

What in New Testament invalidates God’s commandments from the Old Testament?

Why is a mixture of two normally distributed variables only bimodal if their means differ by at least two times the common standard deviation?

Why does the Antonov AN-225 not have any winglets?

What is the right approach to quit a job during probation period for a competing offer?

Are there any sports for which the world's best player is female?

Is it okay to roll multiple attacks that all have advantage in one cluster?

When did "&" stop being taught alongside the alphabet?

Write a function

What happens when adult Billy Batson says "Shazam"?

Why does wrapping Aluminium foil around my food help it keep warm, aluminium be good conductor should have no effect?

Party going through airport security at separate times?

What minifigure is this?

To what extent would a wizard be able to combine feats to learn to mimic unknown spells?

Does a wizard need their hands free in order to cause their familiar from the Find Familiar spell to reappear?

Misspelling my name on my mathematical publications

A horrible Stockfish chess engine evaluation

Yet another hash table in C

The origin of a particular self-reference paradox

Postgres Trigram acting strange for specific characters

SQL Server 2005: Full-text search, order by matchesERROR: text search configuration name “english” must be schema-qualifiedHow to set weights on a Postgres TSVECTOR column?Oracle “matches” multiple columnsPostgres pg_trgm JOIN multiple columns with large tables (~50 million rows)Return matches that have at least one messageFulltext match() against() returning empty setOptimize a trigram search with custom sort orderSlow query times for similarity searches with pg_trgm indicesHow can I find all rows that have at least one element in an array matching a pattern?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query:

SELECT my_column, similarity('$ Hello', my_column) AS sml
FROM my_table
WHERE my_column % '$ Hello'
ORDER BY sml DESC, my_column;

In my_table, I have the following:

- Hello
? Hello
| Hello
$ Hello
! Hello
!? Hello

They all return with a similarity match of 1. Do I need to escape the $ or something similar?

postgresql full-text-search

share|improve this question

asked 8 hours ago

ThePowerThePower

10622 bronze badges

New contributor

ThePower is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

1

I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query:

SELECT my_column, similarity('$ Hello', my_column) AS sml
FROM my_table
WHERE my_column % '$ Hello'
ORDER BY sml DESC, my_column;

In my_table, I have the following:

- Hello
? Hello
| Hello
$ Hello
! Hello
!? Hello

They all return with a similarity match of 1. Do I need to escape the $ or something similar?

postgresql full-text-search

share|improve this question

asked 8 hours ago

ThePowerThePower

10622 bronze badges

New contributor

ThePower is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query:

SELECT my_column, similarity('$ Hello', my_column) AS sml
FROM my_table
WHERE my_column % '$ Hello'
ORDER BY sml DESC, my_column;

In my_table, I have the following:

- Hello
? Hello
| Hello
$ Hello
! Hello
!? Hello

They all return with a similarity match of 1. Do I need to escape the $ or something similar?

postgresql full-text-search

share|improve this question

asked 8 hours ago

ThePowerThePower

10622 bronze badges

New contributor

ThePower is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

SELECT my_column, similarity('$ Hello', my_column) AS sml
FROM my_table
WHERE my_column % '$ Hello'
ORDER BY sml DESC, my_column;

- Hello
? Hello
| Hello
$ Hello
! Hello
!? Hello

share|improve this question

asked 8 hours ago

ThePowerThePower

10622 bronze badges

New contributor

ThePower is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 8 hours ago

ThePowerThePower

10622 bronze badges

New contributor

ThePower is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

ThePowerThePower

10622 bronze badges

New contributor

ThePower is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

ThePowerThePower

10622 bronze badges

2 Answers
2

active

oldest

votes

2

SELECT show_trgm('$ Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

SELECT show_trgm('- Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

All non-alphanumeric characters are stripped from a string before the trigrams are calculated.

That's why the strings have similarity 1.

You cannot use trigram indexes for searching for symbols.

The documentation has it in its first sentence (emphasis mine):

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching

share|improve this answer

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

add a comment | 

2

Ignoring punctuation is how pg_trgm works, as documented.

If you are feeling adventurous, you can remove the line #define KEEPONLYALNUM from "contrib/pg_trgm/trgm.h", recompile and reinstall.

This will silently corrupt any existing pg_trgm indexes you have, you will need to reindex them.

share|improve this answer

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges

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



);

ThePower is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f242356%2fpostgres-trigram-acting-strange-for-specific-characters%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

SELECT show_trgm('$ Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

SELECT show_trgm('- Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

All non-alphanumeric characters are stripped from a string before the trigrams are calculated.

That's why the strings have similarity 1.

You cannot use trigram indexes for searching for symbols.

The documentation has it in its first sentence (emphasis mine):

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching

share|improve this answer

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

add a comment | 

2

SELECT show_trgm('$ Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

SELECT show_trgm('- Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

All non-alphanumeric characters are stripped from a string before the trigrams are calculated.

That's why the strings have similarity 1.

You cannot use trigram indexes for searching for symbols.

The documentation has it in its first sentence (emphasis mine):

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching

share|improve this answer

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

add a comment | 

SELECT show_trgm('$ Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

SELECT show_trgm('- Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

All non-alphanumeric characters are stripped from a string before the trigrams are calculated.

That's why the strings have similarity 1.

You cannot use trigram indexes for searching for symbols.

The documentation has it in its first sentence (emphasis mine):

The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching

share|improve this answer

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

SELECT show_trgm('$ Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

SELECT show_trgm('- Hello');

 show_trgm 
---------------------------------
 " h"," he",ell,hel,llo,"lo "
(1 row)

share|improve this answer

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

answered 7 hours ago

Laurenz AlbeLaurenz Albe

1,6271515 bronze badges

2

Ignoring punctuation is how pg_trgm works, as documented.

If you are feeling adventurous, you can remove the line #define KEEPONLYALNUM from "contrib/pg_trgm/trgm.h", recompile and reinstall.

This will silently corrupt any existing pg_trgm indexes you have, you will need to reindex them.

share|improve this answer

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges

add a comment | 

2

Ignoring punctuation is how pg_trgm works, as documented.

If you are feeling adventurous, you can remove the line #define KEEPONLYALNUM from "contrib/pg_trgm/trgm.h", recompile and reinstall.

This will silently corrupt any existing pg_trgm indexes you have, you will need to reindex them.

share|improve this answer

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges

add a comment | 

Ignoring punctuation is how pg_trgm works, as documented.

If you are feeling adventurous, you can remove the line #define KEEPONLYALNUM from "contrib/pg_trgm/trgm.h", recompile and reinstall.

This will silently corrupt any existing pg_trgm indexes you have, you will need to reindex them.

share|improve this answer

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges

share|improve this answer

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges

answered 7 hours ago

jjanesjjanes

15.8k11 gold badge1010 silver badges1818 bronze badges