Postgres Pro
Downloads
Home   >   mailing lists

How to Handle ltree path Data Type - Mailing list psycopg

From Don Parris
Subject How to Handle ltree path Data Type
Date
Msg-id CAJ-7yoks2eowLAc2k==XjFJU=1JqJgyJgCWQQmCD0NLtdK=Vog@mail.gmail.com
Whole thread Raw
Responses Re: How to Handle ltree path Data Type
List psycopg
Tree view
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
GPG Key ID: F5E179BE

psycopg by date:

Previous
From: Don Parris
Date:
Subject: Re: Character Encoding Question
Next
From: Daniele Varrazzo
Date:
Subject: Re: How to Handle ltree path Data Type