Re: Integer fields and auto-complete clients

> On Oct 26, 2016, at 6:59 AM, Tim Smith <randomdev4+postgres(at)gmail(dot)com> wrote:
>
> Hi,
>
> I'm curious as to what the current advice would be in relation to
> auto-complete type applications (e.g. "AJAX" type java-script "guess
> as you type" applicatoins).
>
> In relation to text fields, I know the general suggestion is gin_trgm_ops.
>
> Is there much point even thinking about using gin_trgm_ops on integers
> ? I'm thinking perhaps the 'prefix' extension is better suited ? Or
> is there something else altogether I should be considering to support
> such applications ?

It depends on whether you want to return results that have the typed value
as a prefix or results that include the typed value as a substring. i.e. where
foo like 'bar%' vs where foo like '%bar%'.

If the latter, pg_trgm is the way to go. If the former then a regular btree index
on the (case-folded text form of the) value, possibly using text_pattern_ops, is the right thing.

The prefix module isn't what you want - it's for matching, e.g., an entire phone number
against a table of possible prefixes, not a prefix against a table of possible matches.

Cheers,
Steve