Re: How to Handle ltree path Data Type

On Fri, Apr 5, 2013 at 2:35 AM, Don Parris <parrisdc@gmail.com> wrote:
> Hi all,
>
> Do any of you have experience with ltree?  Note: this may be more of a
> general Python3 question, but I thought I would start here, since I use
> Psycopg to access my DB.

I'm working on a project using them heavily and have no problem with them.

> psycopg2.ProgrammingError: operator does not exist: ltree ~* unknown
> LINE 1: SELECT ltree2text(path) FROM category WHERE path ~* 'income'...
>                                                          ^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> If I remove the asterisk, that appears to resolve the issue.  However, I
> only get results when searching on the first category in the structure.  So
> I am still not quite "there".

~* is not an ltree operator (they are listed at
http://www.postgresql.org/docs/9.2/static/ltree.html#SECT2). If you
cast from ltree to text and try to use the text operators you don't
get any gain from it, e.g. from a gist index you could build on the
column.