在PostgreSQL中验证LTREE等级

46qrfjad 于 2023-01-30 发布在 PostgreSQL

关注(0)|答案(1)|浏览(135)

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

postgresql

来源：https://stackoverflow.com/questions/75264832/validating-ltree-hierarchies-in-postgresql

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

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）

赞(0）回复(0）举报 2023-01-30

我来回答

在PostgreSQL中验证LTREE等级

1条答案

相关问题

热门标签

最新问答