一般来说,我对层次结构,特别是LTREE是个新手,当我将一列基于文本的层次结构转换并加载到LTREE列中时,我注意到一个格式很差的字符串。
create table test_tree(id int, path ltree);
insert into test_tree values (1, '1');
insert into test_tree values (1, '1.1');
insert into test_tree values (1, '1.2.0'); --should be '1.2'
insert into test_tree values (1, '1.2.1');
insert into test_tree values (1, '1.2.2.0'); --should be '1.2.2'
insert into test_tree values (1, '1.2.2.1');
insert into test_tree values (1, '1.2.2.2');
这会导致一些意外的行为。
select path from test_tree where path <@ '1';
返回后代,即:
1
1.1
1.2.0
1.2.1
1.2.2.0
1.2.2.1
1.2.2.2
鉴于:
select path from test_tree where path @> '1.2.2.2';
只有回报
1.2.2.2
我期望<@ '1'
返回与@> '1.2.2.2'
一致的结果。在这种情况下,祖先如何知道它的后代,而后代却不知道它的祖先?为什么<@ '1'
返回所有后代(似乎忽略了缺失的'1.2.2'
),而@> '1.2.2.2'
不返回祖先?
此外,如何在LTREE数据类型中找到这些缺失的关系?
1条答案
按热度按时间blmhpbnm1#
ltree运算符并不关心表中有没有值,它们只比较两个ltree值。
'1' @> '1.2.2.2'
为真,'1.2.2.2' @> '1.2.2.2'
为真,'1.1' @> '1.2.2.2'
为假。但是
SELECT
查询只返回表中实际存在的行。'1.2' @> '1.2.2.2'
和'1.2.2' @> '1.2.2.2'
本来也会为true,但是这两个值不存在于表中,因此无法找到它们。@>
/<@
运算符不会构造新行。要实际构造ltree值的所有可能祖先,而不仅仅是表中的祖先,可以使用
(online demo)
您似乎还假设了使用
ltree
列时的一个隐式约束,即父值存在于表的同一列中,但这是不可能的,因为在关系数据库中,行是相互独立的:ltree
值不是对另一行加上最后一个标签的引用,它实际上只是一个标签列表;表中的每一行都存储了完整的标签路径。对列使用特定类型不能引入约束,您必须自己做-作为生成列中的复杂外键,或者使用触发器。如何在LTREE数据类型中找到这些缺失的关系?
您可以使用以下命令在 * table *(而不是 * datatype *)中查找此类缺失的 * rows
(online demo)