How to Handle ltree path Data Type

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 have implemented a hierarchical category structure using the ltree module
in my PostgreSQL db.

The category table uses ltree like so:
CREATE TABLE category(
cat_id serial pk
path ltree);

The path field takes data in the form of:
Level1.Level2.Level3.Level4.etc.

I can select * from category and get a standard result set. However, I
cannot seem to perform a search for a specific category or branch of the
category tree. At least, I only get a result set when searching on the
first branch of categories in the table. I believe I need to use type
casting, but am not sure.

Ultimately, I want the user to be able to search for and select the desired
category from the result list, and then use that selection to insert the
correct cat_id into the appropriate column in another table.

Below is a more detailed description of what I am trying to do...

I can do this in SQL:
> SELECT path FROM test WHERE path <@ 'Top.Science';

and get a result like this (from the documentationl):
Top.Science
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology

I get the impression that I need to cast the ltree path field to text, but
I am not certain.

In an early iteration of this effort, I got the following result when
running my program:

Search for Category: Income //Prompt to collect search term
--------------
Traceback (most recent call
last):

File "dev/ldinero/ldinero.py", line 74, in
<module>

category_search()
File "/home/donp/dev/ldinero/ldinero_conn.py", line 29, in category_search
{'term': search_term})
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".

Thanks,
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