I am trying to optimize query with the clause:

WHERE LOWER(CAST(\"my_table\".\"id\" as TEXT) LIKE '%456%'

I decided to create a trigram index for it.

CREATE INDEX index_name ON my_table USING gin (id gin_trgm_ops);

But looks like I can't do it for an integer type:

ERROR:  operator class "gin_trgm_ops" does not accept data type bigint

Is it possible? Probably some typecast required during index creation...

  • LIKE isn't intended for numbers. This really looks like a very strange query. – a_horse_with_no_name Sep 30 at 11:14
  • this is the query for search ids by its substring, ids are integers – Maxim Yefremov Sep 30 at 11:17
  • 1
    I understand what you are doing. But I don't understand why someone would want to search for "substrings" of a number. I can't picture the use case. Btw: LOWER(CAST(\"my_table\".\"id\" as TEXT) can be simplified to my_table.id::text – a_horse_with_no_name Sep 30 at 11:19

You would have to create the trigram index on the complete expression left of the LIKE operator.

The lower is unnecessary, and you should omit it.

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.