Integer fields and auto-complete clients

On Oct 26, 2016, at 6:59 AM, Tim Smith <randomdev4+postgres@gmail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general