Querying PostgreSQL ltree paths against an array of regex-like lquerys

Alessandro Bolletta
Sep 7, 2018 · 1 min read

Since we decided to use ltree type on PostgreSQL, in order to store hierarchical informations about entities for the sake of simplicity, we even were often in trouble just trying to understand how to properly get them working as expected. Lack of documentation makes things more complex than they should.

For example, today we needed to query an ltree path in order to SELECT the ltrees that were matching not only one condition (called lquery) but an array of them (lquery[]).

So, after a day passed to figure out why the queries that you can find in the official documentation (https://www.postgresql.org/docs/9.6/static/ltree.html) were always returning syntax-related errors, finally we could find out how to manage them.

In fact, if you need to check an ltree label against an array of lquery, you should cast them before passing them to the comparison operator:

SELECT path FROM test WHERE path ? ARRAY[‘*.Hobbies.*’,‘*.Collections.*’]::lquery[];

Alessandro Bolletta

Written by

Cloud Computing enthusiast, newbie coder, CEO @ Netter.io