Postgres - casting text to ltree

Question

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

• https://www.postgresql.org/docs/9.1/static/ltree.html
• https://www.postgresql.org/docs/9.4/static/functions-formatting.html

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!

Answer 1

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;