CRDB compatibility with Postgres LTREE data type

[florence-crl]

Feature requested in forum. CRDB compatibility with Postgres LTREE data type as described here

MVP Implementation:

• LTREE datatype and datum integration
• LTREE containment operators
• LTREE subpath(), nlevel(), subltree(), lca(), index() functions
• Support index acceleration for LTREE comparison operators and containment operators

Additional Feature Implementation:

• LTREE matching with LQUERY
• LTREE matching with LTXTQUERY

Epic: CRDB-148
Jira issue: CRDB-5214

[jlgerber]

Ltree has proven to be a really good way of materializing hierarchies for us.

For example, we are in M&E, and have a number of asset management systems that utilize ltree to associate assets with a project level (show.sequence.shot). Using the ltree extension, we are able to efficiently look up all the assets in a particular show, or sequence in the show, or shot,etc, without resorting to recursive joins, or the more cumbersome solutions (found for instance in Joe Celko's Trees and Hierarchies bool)

[chickahoona]

I myself would be interested to see this too. With Psono I'd like to explore alternative databases that allow a georedundant setup more easily and CockroachDB is sadly not possible as ltree doesn't work :(

[cbollerud]

Is Ltree still under consideration? It has been a useful feature with several companies to quickly select on a subset of a hierarchy.

[alen-z]

Bumping this as a deciding factor to move to CRDB.

[koudelka]

Also interested in this!