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 <[email protected]>: > Will take a look. Thanks steve. > > On 24 February 2015 at 23:57, Steve Atkins <[email protected]> wrote: > > > > On Feb 24, 2015, at 3:50 PM, Tim Smith <[email protected]> > 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 ([email protected]) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
