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.
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}
