Rocking ltree on postgresql

Postgresql 9.1 Now ships with a module called ltree. This is a cool postgresql that allows you to model hierarchies in you database and leverages the GIST indexing mechanism built into postgres. Assume you are modeling several organizations:

CREATE TABLE orgs (path ltree);
INSERT INTO orgs VALUES ('Top');
INSERT INTO orgs VALUES ('Top.OrgA');
INSERT INTO orgs VALUES ('Top.OrgA.Division1');
INSERT INTO orgs VALUES ('Top.OrgA.Division1.storeZ');
INSERT INTO orgs VALUES ('Top.OrgA.Division1.storeY');
INSERT INTO orgs VALUES ('Top.OrgA.Division2');
INSERT INTO orgs VALUES ('Top.OrgA.Division2.storeV');
INSERT INTO orgs VALUES ('Top.OrgA.Division2.storeW')
CREATE INDEX path_gist_idx ON test USING gist(path);
CREATE INDEX path_idx ON test USING btree(path);

Now you can do some interesting queries such as Give me all the rstores in Division1 select * from orgs where path ~ "Top.OrgA.Division1.*" or give me all the stores (where the path is at least 4 items long) select * from orgs where path ~ Top.{4}

This is another neat feature that Postgresql gives you but mysql doesn't

October 25, 2012| Tags : postgres postgresql databases
X

Comments Section

Feel free to comment on the post but keep it clean and on topic.

comments powered by Disqus

About Me

Tommie Jones Software Development for Linux/Unix since 1995. I've done everything from Perl,C/C++,Java,Flash,php, ruby and currently node.js. Always interested in pushing technology one step further than expected.

atlantageek.github.com