Le ven. 3 nov. 2023 à 21:01, Tom Lane <tgl@xxxxxxxxxxxxx> a écrit :
David Gauthier <dfgpostgres@xxxxxxxxx> writes:
> I'm asking about the possibility of indexing portions of a column's value
> where the column has a static field format.
GIN indexes are meant for exactly that. You might have to write your
own opclass to break up the input values in the way you want though.
A less difficult answer would be to write a function that breaks up
the input into (say) an array of text and then use the existing
GIN array support. But you'd pay for that by needing to write more
complicated queries to use the index.
regards, tom lane
Hello David,
Reading your mail, it seems that your data column contains 3 different kinds of atomic information:
* Characters 1-2
* Characters 3-4
* Characters 5-8
Does it make sense to split this data into 3 separate columns?
Each one could be indexed, and you can rebuild the original thanks to a generated column: https://www.postgresql.org/docs/current/ddl-generated-columns.html
HTH, Thomas
