Обсуждение: 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.

From my experience so far, you can do pretty much everything just
using the ~ operator (ltree ~ lquery, not the text one). E.g. your
example query can be expressed as "SELECT path FROM test WHERE path ~
'Top.Science.*';" All the other operators are noise, I don't use any
of them (except the || for concatenation, no other search operator).

From psycopg you may need sometimes to use an explicit ::lquery cast
on a placeholder; apart from that you can just work with strings on
the Python side. In a long time I've been using it, I've found no use
for a specific object wrapping ltree/lquery.

    cur.execute("SELECT path FROM test WHERE path ~ %s::lquery",
["Top.Science.*"])

in this case the lquery cast would not have been needed: the query
would have worked anyway. You need it when the lquery is put together
on postgres side instead of python side, such as:

    cur.execute("SELECT path FROM test WHERE path ~ (%(prefix)s ||
'.*')::lquery", {prefix: "Top.Science"})

the cast is mandatory, because postgres can infer the operator from
ltree ~ unknown but (%(prefix)s || '.*') is a text expression and
there is no cast allowing ltree ~ text to work). In these cases the
error message is clean: it couldn't find the operator "ltree ~ text",
so you know you have to cast the text to lquery.

Hope this helps.

-- Daniele

On Fri, Apr 5, 2013 at 11:51 PM, Don Parris <parrisdc@gmail.com> wrote:

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

You are probably confusing text ~ text (regexp espression) with ltree
~ lquery, which are indeed two different operators. If you want to
search the ltrees with the label 'term' in any position you must match
the lquery '*.term.*'. Try:

    cur.execute("""SELECT * FROM category WHERE path ~ %(query)s;""",
        {'query': '*.%s.*' % search_term})

-- Daniele