How to join a Postgres ltree to a table of labels?

Question

What is the recommended way of using a Postgres ltree common to store product categories?

For example, my column could contain an ltree path such as "1.2.3" where 1, 2, and 3 are foreign keys into a table of category labels which can be displayed to the user:

categories

id | name
---+-----------
 1 | Hardware
---+-----------
 2 | Computers
---+-----------
 3 | Video Cards
---+-----------

Now, for a given product, I would like to select its category and materialize it like "Hardware > Computers > Video Cards".

Answer 1

In PG 9.4+:

SELECT p.id, string_agg(c.name, ' > ' ORDER BY t.ord) AS label
FROM product p
JOIN regexp_split_to_table(p.category::text, '[.]') WITH ORDINALITY t(category, ord) ON true
JOIN categories c ON c.id = t.category::int
GROUP BY p.id;

This line:

regexp_split_to_table(p.category::text, '[.]') WITH ORDINALITY t(category, ord)

takes the ltree column and then breaks it up into rows, one for each element in the ltree. The WITH ORDINALITY clause will add a row number to the output, here with alias ord. That row number is used in the string_agg() function to keep the category labels in their correct order.

If you are using an older version of PG (9.0+) then (you should upgrade or else) you should do:

SELECT p.id, string_agg(c.name, ' > ' ORDER BY t.ord) AS label
FROM product p
JOIN generate_series(1, nlevel(p.category)) t(ord) ON true
JOIN categories c ON c.id = subltree(p.category, t.ord - 1, t.ord)::text::int
GROUP BY p.id;

This is less efficient because the ltree has to be parsed for every individual element contained within it (subltree(...)).