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)


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.