在PostgreSQL中验证LTREE等级

46qrfjad  于 2023-01-30  发布在  PostgreSQL
关注(0)|答案(1)|浏览(133)

一般来说,我对层次结构,特别是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数据类型中找到这些缺失的关系?

blmhpbnm

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值的所有可能祖先,而不仅仅是表中的祖先,可以使用

SELECT subpath(p, 0, generate_series(1, nlevel(p)))

online demo
您似乎还假设了使用ltree列时的一个隐式约束,即父值存在于表的同一列中,但这是不可能的,因为在关系数据库中,行是相互独立的:ltree值不是对另一行加上最后一个标签的引用,它实际上只是一个标签列表;表中的每一行都存储了完整的标签路径。对列使用特定类型不能引入约束,您必须自己做-作为生成列中的复杂外键,或者使用触发器。
如何在LTREE数据类型中找到这些缺失的关系?
您可以使用以下命令在 * table *(而不是 * datatype *)中查找此类缺失的 * rows

SELECT path, array_agg(id) AS required_by
FROM (
  SELECT id, subpath(path, 0, generate_series(1, nlevel(path)-1)) AS path
  FROM test_tree
  ORDER BY path, id
) AS all_parent_paths
WHERE NOT EXISTS (SELECT * FROM test_tree WHERE path = all_parent_paths.path)
GROUP BY path

online demo

相关问题