2

Im new to postgres and am looking to utilize ltree for a hierarchal data structure.

I have the both the data and the ltree structure (ie domain.class.sublass) in varchar columns, and need to cast the ltree structure into another column with the proper ltree data type.

Ive identified text2ltree as the best way to likely manage this but havent made much headway even after reading the ltree documentation and ltree function information

Ive tried many combinations of the below 

 alter table codes
alter column joinedclassname type ltree using text2ltree(joinedclassname);

but receive the following error:

ERROR:  syntax error at position 26
********** Error **********

ERROR: syntax error at position 26
SQL state: 42601

Ive also tried the following and recieved the same error:

alter table codes
alter column joinedclassname type ltree using joinedclassname::ltree;

How do I cast a ltree hiearchary from a varchar column to a column with a ltree data type?

Thanks!

user17104
  • 93
  • 1
  • 6
  • [does this example help?](https://coderwall.com/p/whf3-a/hierarchical-data-in-postgres) – Paul Maxwell Jan 19 '18 at 04:00
  • Are you aware of recursive queries? https://www.postgresql.org/docs/current/static/queries-with.html –  Jan 19 '18 at 08:26
  • your actions sequence is ok. error occures at word `column` - I suspect hidden symbols or smth. try this line: `alter table codes alter column joinedclassname type ltree using path::ltree;` as it - without adding extra spaces or such – Vao Tsun Jan 19 '18 at 08:26
  • Hi vao tsun, thanks for taking the time. I tried your suggestion, same outcome I updated my question to show this also. – user17104 Jan 19 '18 at 22:53

1 Answers1

6

Figured it out:

ltree doesnt play well with hyphens and whitespace. Once I concatenated the value and stripped out the hyphens and whitespace, it worked fine.

SELECT text2ltree(replace(concat_ws('.', Systemname::text, SubsystemName::text,ClassName::text,SubclassName::text),'-','_')) FROM codes;
user17104
  • 93
  • 1
  • 6