Re: How to Handle ltree path Data Type

On Fri, Apr 5, 2013 at 6:31 AM, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com
> wrote:

> On Fri, Apr 5, 2013 at 2:35 AM, Don Parris <parrisdc(at)gmail(dot)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.
>

Thanks Daniele,

I realized that when I looked at the documentation, and changed it. As I
mentioned, I did get the search partially working. However, I now wonder
if I am posing my user-generated query in the wrong way:

search_term = input('Search for Category: ') # get input from user
cur = con.cursor()
cur.execute("""SELECT * FROM category WHERE path ~ %(term)s;""", # Run
select query against the user's search term
{'term': search_term})

If I run the above query, using the very first item in the category table
as a search term, I will get a result. If I use any other term below that,
I get no result at all. This closely mirrors my search queries against
other tables, but apparently does not work quite the same in this case.
This is why I thought maybe it had to do with the ltree data type.

I can also run the query like so (with the same results as I stated above):
cur.execute("""SELECT * FROM category WHERE path <@ %(term)s;""", # Run
select query against the user's search term
{'term': search_term}

Regards,
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE