Database Reference
In-Depth Information
Label tree - ltree
It provides a way to organize data with labels stored in a hierarchical tree structure. The
main advantage of this extension is the speed for searching data, since a recursive search is
not necessary.
For you to understand this extension better, you can build an example of continents and
countries in a hierarchical tree as shown in the following diagram:
Hierarchical tree of countries.
First, you will install the
ltree
extension, then create the table of countries and add the
tree structure as shown in the preceding diagram. The commands for creating the country
table are as follows:
$ heroku pg:psql --app your-app-name
CREATE EXTENSION ltree;
CREATE TABLE countries (path ltree);
INSERT INTO countries VALUES ('Earth');
INSERT INTO countries VALUES ('Earth.Africa');
INSERT INTO countries VALUES ('Earth.Africa.Nigeria');
INSERT INTO countries VALUES ('Earth.Africa.Angola');
INSERT INTO countries VALUES ('Earth.Europe');
INSERT INTO countries VALUES ('Earth.Europe.France');
INSERT INTO countries VALUES ('Earth.Asia');
INSERT INTO countries VALUES ('Earth.Asia.Japan');
INSERT INTO countries VALUES ('Earth.Asia.China');
Finally, you are able to perform queries to discover the countries on each continent. For ex-
ample if you want to view countries of Africa use the following query:
SELECT path FROM countries WHERE path ~ 'Earth.Africa.*{1}';
path
----------------------
