Some other solutions http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II#Fast_searching_of_longer_prefix
2015-02-25 9:04 GMT+01:00 Tim Smith <randomdev4+postg...@gmail.com>: > Will take a look. Thanks steve. > > On 24 February 2015 at 23:57, Steve Atkins <st...@blighty.com> wrote: > > > > On Feb 24, 2015, at 3:50 PM, Tim Smith <randomdev4+postg...@gmail.com> > wrote: > > > >> > >> > >> The goal being to match the longest prefix given a full phone number, > e.g. > >> > >> > >> 61234567890 would match "australia proper 61" > >> whilst > >> 61134567890 would match "Australia premium 6113" > >> and > >> 61894321010 would match "Australia - Sydney 61893" > >> > >> I know the answer involves Postgres CTE, but I haven't used CTEs much > >> yet... let alone in complex queries such as this. > >> > >> Thanking you all in advance for your kind help. > > > > There's probably a CTE approach for it, but you might want to look > > at https://github.com/dimitri/prefix too - it's an extension that's > designed > > specifically for longest prefix matching, and that uses gist indexes to > > do it efficiently. > > > > 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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
