Postgresql – How to index a text field that requires explicit characters and wildcards on both ends (postgres)

postgresql

I would like to search for a tracking number while only having partial of the value.

I have a tracking number "1Z1234abcde" in my table.

I want to be able to:
select * from tracking_numbers where value ILIKE '%abc%';

I currently have this as a 'text' field type in my table but I am not able to index it correctly so I'm doing a Seq Scan when I use those wildcards.

                                             Table "public.tracking_numbers"
          Column          |            Type             | Collation | Nullable |                   Default
--------------------------+-----------------------------+-----------+----------+----------------------------------------------
 id                       | integer                     |           | not null | nextval('tracking_numbers_id_seq'::regclass)
 value                    | character varying           |           |          |

Full text search and trigram do not solve my problem because I need the characters to be exact, not variations. (ie. an exact tracking_number)

Best Answer

trigram indexes are exactly what you do want. In ILIKE '%abc%', the '%' invite variation, while the 'abc' are exactly 'abc'. Maybe you are thinking of the <-> and % operators, which trigram indexes also support. But you don't need to use them if you don't want to, you can just use ILIKE.