在PostgreSQL中是否有处理无序数组(集合)的标准方法?[英] Is there a standard approach for dealing with unordered arrays (sets) in PostgreSQL?

本文是小编为大家收集整理的关于在PostgreSQL中是否有处理无序数组(集合)的标准方法?的处理方法,想解了在PostgreSQL中是否有处理无序数组(集合)的标准方法?的问题怎么解决?在PostgreSQL中是否有处理无序数组(集合)的标准方法?问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有一个表,其中包含两列中的成对单词.单词的顺序通常很重要,但有时我只想根据两个单词进行聚合,而不考虑顺序.有没有一种简单的方法可以将具有相同单词但顺序不同的两行(一行与另一行相反)视为相同的"集合"?换句话说,对待:

apple orange
orange apple

作为:

(apple,orange)
(apple,orange)

推荐答案

目前没有内置方式.

作为数组

如果您在保存时始终对它们进行规范化,则可以将数组视为集合,始终将它们存储为排序和重复数据删除.如果 PostgreSQL 有一个内置的 C 函数来做到这一点,那就太好了,但它没有.我看了看写一个,但 C 数组 API 可怕,所以即使我已经写了一堆扩展,我只是小心翼翼地远离这个.

如果您不介意中等 icky 的性能,您可以在 SQL 中完成:

CREATE OR REPLACE FUNCTION array_uniq_sort(anyarray) RETURNS anyarray AS $$
SELECT array_agg(DISTINCT f ORDER BY f) FROM unnest($1) f;
$$ LANGUAGE sql IMMUTABLE;

然后将所有保存包装在对 array_uniq_sort 的调用中或使用触发器强制执行.然后,您可以比较您的数组是否相等.如果您只是在应用程序端进行排序/唯一,则可以避免 array_uniq_sort 从应用程序调用数据.

如果您这样做请将您的"集合"存储为数组列,例如 text[],而不是逗号或空格分隔的文本.部分原因请参见这个问题.

您需要注意一些事情,例如数组之间的强制转换比它们的基本类型之间的强制转换更严格.例如:

regress=> SELECT 'a' = 'a'::varchar, 'b' = 'b'::varchar;
 ?column? | ?column? 
----------+----------
 t        | t
(1 row)

regress=> SELECT ARRAY['a','b'] = ARRAY['a','b']::varchar[];
ERROR:  operator does not exist: text[] = character varying[]
LINE 1: SELECT ARRAY['a','b'] = ARRAY['a','b']::varchar[];
                              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
regress=> SELECT ARRAY['a','b']::varchar[] = ARRAY['a','b']::varchar[];
 ?column? 
----------
 t
(1 row)

对于数组包含或数组重叠等操作,此类列是 GiST 可索引的;请参阅有关数组索引的 PostgreSQL 文档.

作为规范化行

另一种选择是仅使用合适的键存储规范化的行.我仍然会使用 array_agg 对它们进行排序和比较,因为使用 SQL 集合操作可能很笨拙(尤其是在缺少 XOR/双面集合差异操作的情况下).

这通常称为 EAV(实体属性值).我自己不是粉丝,但它确实偶尔有它的位置.除非您在没有 value 组件的情况下使用它.

你创建一个表:

CREATE TABLE item_attributes (
    item_id integer references items(id),
    attribute_name text,
    primary key(item_id, attribute_name)
);

并为每个项目的每个集合条目插入一行,而不是让每个项目都有一个数组值列.主键强制执行的唯一约束确保没有项目可以具有给定属性的重复项.属性排序无关/未定义.

可以使用EXCEPT 等SQL 集合运算符进行比较,或使用array_agg(attribute_name ORDER BY attribute_name) 形成一致排序的数组进行比较.

索引仅限于确定给定项目是否具有/不具有给定属性.

我个人会使用数组而不是这种方法.

hstore

您还可以使用具有空值的 hstore 来存储集合,因为 hstore 会删除重复键.9.4 的 jsonb 也适用于此.

regress=# create extension hstore;
CREATE EXTENSION
regress=# SELECT hstore('a => 1, b => 1') = hstore('b => 1, a => 1, b => 1');
 ?column? 
----------
 t
(1 row)

不过,它只对文本类型真正有用.例如:

regress=# SELECT hstore('"1.0" => 1, "2.0" => 1') = hstore('"1.00" => 1, "1.000" => 1, "2.0" => 1');
 ?column? 
----------
 f
(1 row)

我认为它很丑.再说一次,我更喜欢数组.

仅适用于整数数组

intarray 扩展提供了有用、快速的函数来将数组视为集合.它们仅适用于整数数组,但非常有用.

本文地址:https://www.itbaoku.cn/post/1763937.html