Re: Parsing of hex encoding strings

On Mon, 2026-10-05 at 12:58 +0200, Thomas de Zeeuw wrote:
> 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.

I could only go by what you told us, and as you said "strings", I assumed you were
talking about character strings, not binary data. Full-text search is no good for
binary data, even if encoded - it is only useful for natural language strings.

> 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.

If all you want to do is prefix matches and exact matches, I would use an
ordinary LIKE with a regular B-tree index. That should work well, unless
the public keys are large. In that case, you could try the ^@ operator
and an SP-GiST index. Or resort to trigrams.

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

No idea what a vector/query is, but good if it works for you.

Yours,
Laurenz Albe