Re: Indexing fragments of a column's value ? - Mailing list pgsql-general

From Kirk Wolak
Subject Re: Indexing fragments of a column's value ?
Date
Msg-id CACLU5mQ9yLe7kZdxR7VfX9ZLdBsx5Xff5w2UnXNhqOzh8wfTBg@mail.gmail.com
Whole thread Raw
In response to Indexing fragments of a column's value ?  (David Gauthier <dfgpostgres@gmail.com>)
List pgsql-general
On Fri, Nov 3, 2023 at 3:34 PM David Gauthier <dfgpostgres@gmail.com> wrote:
I'm asking about the possibility of indexing portions of a column's value where the column has a static field format.  Example, a char(8) which contains all hex values (basically a hex number that's always 8 chars wide, leading zeros if needed). Someone might want to select all recs where the first 2 digits are 'ff' or maybe the last 4 hex digits match regexp_match '00[cdef]{2}' or maybe a match of the entire string... "0dd63a87".

If I know the placement and width of the fields that need to be indexed, can indices be defined to facilitate queries ?  Example...
- match all 8 chars
- match the 3rd and 4th chars
- match the last 4 chars

I suppose I could fragment the thing into multiple columns for the purposes of a search.  So add a column called "last4" as a char(4) that matches the last 4 chars of that column.  Then index that.  etc... But inquiring to see if there is something more elegant.

Just curious why not store it as a HEX string (functional index, even).
And allow searching against the stringified hex, then your regex works?

Kirk 

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Unique Primary Key Linked to Multiple Accounts
Next
From: Alban Hertroys
Date:
Subject: Re: Issue in compiling postgres on latest macOS 14.1.1