PostgreSQL Ltree Type

The last one months when using PostgreSQL, often encountered ltree data, feel a bit awkward, but there is no way around. Today is determined to tidy up, later use more convenient.

First, Introduction

Ltree is an extended type of PostgreSQL, developed jointly by two foreign PostgreSQL contributors. The website is as follows: Http://www.sai.msu.su/~megera/postgres/gist

Second, the purpose

The purpose of this type of development is to solve complex tree-like model-related problems,

Also mentioned in this URL: http://blog.163.com/[email protected]/blog/static/163877040201132843255911/

Third, detailed 3.1 whether to install Ltree

Execute Sql:select * from pg_extension where extname = ' ltree ';

Extname        | ltreeextowner       extnamespace   2200|  Textversion     1.0extconfig      | extcondition   

If not installed, refer to website: http://blog.163.com/[email protected]/blog/static/1638770402011427104710922/

3.2 Definitions

Ltree is a string of tags and separators, such as: L1.L2.L3

• Delimiter: usually a decimal point
• Tags: currently supported by a-za-z0-9_ composed of strings
• Path: The label itself is also a path
• Lquery: A regular expression that can be understood as a search ltree, for example: *.test.*, which represents any path that contains the test tag
• Ltxtquery: Ibid., generally used for full-text scanning.

Four, operator

Operator	return value	Describe
Ltree @> Ltree	Boolean	Is the left-hand side argument the ancestor of the right-hand side (or peers)?
Ltree <@ Ltree	Boolean	Is the left-hand side argument a descendant of the right-hand side (or peers)?
Ltree ~ lquery	Boolean	Ltree match lquery?
Lquery ~ Ltree	Boolean	Ltree match lquery?

v. Examples

PostgreSQL Ltree Type