Re: Parsing of hex encoding strings

Hi Larenz,

Thank you for your response. Not great that the first thing I read that this a design error, especially since you’re not familiar with the entire system. In any case, allow me to expand on why we do it this way.

We have various columns that we want to search, most of them are English text (or at least some kind of human language). However, one of them is a public key encoded as a hex string. We want to search all those columns in single query and since most of them are English text we decided on full text search. We imagined one of the words in the search vector would simply be the hex encoded public key. This actually works pretty well for our use case, with the exception of the case I’ve described in the original email.

The trigram search doesn’t work well for the English text columns we have. We could do the trigram search just for the hex encoded public key, but we thought it would return too many false positives given that we’re only interested in exact and prefixed matches. The similarity searching property of trigrams would be negative here as we could include public keys that’s we're not searching for.

We’ll resort to converting the hex encoded public key to a vector/query by casting instead of using the various functions for this.

> On 5 Oct 2026, at 12:35, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Mon, 2026-10-05 at 11:50 +0200, Thomas de Zeeuw wrote:
>> I’m working on a system where I need to search for hex encoded strings, for
>> which I’m using full text search with the “simple” directory. This is working
>> fine for the most part, but I’ve run into an issue which may or may not be
>> considered a bug.
>>
>> When creating the ts vector and queries I want to achieve that the entire hex
>> encoded string is seen as a single word. For a string such as “fe82fbb28”
>> this works using the “simple” directory. However a string such as “7e82fbb28”
>> is parsed as two words: “7e82” as scientific notation and “fbb28" as numword.
>> I’ve put the output of ts_debug at the bottom of this email for convince.
>>
>> I would have expected that the entire string would be considered as one token
>> considering it doesn’t have any whitespace. And since that token isn’t a valid
>> scientific number as whole (though it can be, just not the examples I’ve
>> shared above) I would expected it be considered a single word (numword).
>> Reading the URL example from chapter 12.5
>> (https://www.postgresql.org/docs/current/textsearch-parsers.html) maybe it
>> could have to two tokens, one for the scientific number (“7e82”) and another
>> numword token for the entire string (7e82fbb28”), this would also work for my
>> use case.
>>
>> Finally, my question: is this considered behaviour a bug or working as intended?
>
> That is working as intended. The problem is that you are trying to use full-text
> search for something that isn't a text. Storing strings in hex encoding was a
> design error. Not only does it keep you from using full-text search, but you
> are also wasting storage space (unless TOAST compression takes care of that).
>
> You could use substring search with pg_trgm, but it is not the same as
> full-text search.
>
> Yours,
> Laurenz Albe