Re: Parsing of hex encoding strings

Hi Laurenz,

> On 5 Oct 2026, at 16:10, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> 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.

You’re right. Since it’s only a single we didn’t really want to bother with having to do filter based on search data or an another operator (e.g. LIKE like you suggest). We also use ranking and having to somehow include the match of this hex string in that function would have been challenging I think. So we took the easy route and included it in the full text search vector. For our use case this works well, even if it’s not designed for it.

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

I should have been more clear, I was referring to the tsquery and tsvector types.

>
> Yours,
> Laurenz Albe

Thank you again Laurenz for taking time to help a random stranger on the internet.