How to Handle ltree path Data Type
От | Don Parris |
---|---|
Тема | How to Handle ltree path Data Type |
Дата | |
Msg-id | CAJ-7yoks2eowLAc2k==XjFJU=1JqJgyJgCWQQmCD0NLtdK=Vog@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: How to Handle ltree path Data Type
|
Список | psycopg |
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:
cat_id serial pk
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.
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".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.
Thanks,
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE
В списке psycopg по дате отправления: