pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedParsing of hex encoding strings
5+ messages / 2 participants
[nested] [flat]
* Parsing of hex encoding strings
@ 2026-10-05 09:50 Thomas de Zeeuw <thomasdezeeuw@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas de Zeeuw @ 2026-10-05 09:50 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
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?
Output of ts_debug:
— Wanted result.
SELECT * FROM ts_debug('fe82fbb28');
+---------+--------------------------+-----------+--------------+------------+-------------+
| alias | description | token | dictionaries | dictionary | lexemes |
+---------+--------------------------+-----------+--------------+------------+-------------+
| numword | Word, letters and digits | fe82fbb28 | {simple} | simple | {fe82fbb28} |
+---------+--------------------------+-----------+--------------+------------+-------------+
— Unwanted scientific notation.
SELECT * FROM ts_debug('7e82fbb28');
+---------+--------------------------+-------+--------------+------------+---------+
| alias | description | token | dictionaries | dictionary | lexemes |
+---------+--------------------------+-------+--------------+------------+---------+
| sfloat | Scientific notation | 7e82 | {simple} | simple | {7e82} |
| numword | Word, letters and digits | fbb28 | {simple} | simple | {fbb28} |
+---------+--------------------------+-------+--------------+------------+---------+
With kind regards,
Thomas de Zeeuw
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Parsing of hex encoding strings
@ 2026-10-05 10:35 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Thomas de Zeeuw <thomasdezeeuw@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2026-10-05 10:35 UTC (permalink / raw)
To: Thomas de Zeeuw <thomasdezeeuw@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Parsing of hex encoding strings
@ 2026-10-05 10:58 Thomas de Zeeuw <thomasdezeeuw@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas de Zeeuw @ 2026-10-05 10:58 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
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.albe@cybertec.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Parsing of hex encoding strings
@ 2026-10-05 14:10 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Thomas de Zeeuw <thomasdezeeuw@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2026-10-05 14:10 UTC (permalink / raw)
To: Thomas de Zeeuw <thomasdezeeuw@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Parsing of hex encoding strings
@ 2026-10-06 09:29 Thomas de Zeeuw <thomasdezeeuw@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas de Zeeuw @ 2026-10-06 09:29 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
Hi Laurenz,
> On 5 Oct 2026, at 16:10, Laurenz Albe <laurenz.albe@cybertec.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-10-06 09:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-05 09:50 Parsing of hex encoding strings Thomas de Zeeuw <thomasdezeeuw@gmail.com>
2026-10-05 10:35 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-05 10:58 ` Thomas de Zeeuw <thomasdezeeuw@gmail.com>
2026-10-05 14:10 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-06 09:29 ` Thomas de Zeeuw <thomasdezeeuw@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox