在PostgreSQL中是否有处理无序数组（集合）的标准方法？

Question

我有一个表，在两个单独的列中包含成对的单词。单词的顺序通常很重要，但是有时我只是想根据两个单词进行聚合，而与顺序无关。是否有一种简单的方法将具有相同单词但具有不同顺序（一行与另一行相反）的两行视为相同的“集合”？换句话说，请对待：

apple orange
orange apple

作为：

(apple,orange)
(apple,orange)

Answer 1

目前没有内置方法。

作为数组

如果您在保存时始终对它们进行标准化，则可以通过始终对数组进行排序和重复数据删除来将它们视为集合。如果PostgreSQL有一个内置的C函数可以做到这一点，那是很好的，但是事实并非如此。我看了写一个，但是C数组API
太糟糕了 ，所以即使我写了很多扩展，我也小心翼翼地退出了这个扩展。

如果您不介意中等的性能，可以使用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)
);

并为每个项目的每个set条目插入一行，而不是让每个项目都有一个数组值列。由主键强制执行的唯一约束可确保没有任何项目可以具有给定属性的重复项。属性顺序无关/不确定。

可以使用SQL集运算符（例如）进行比较，也可以EXCEPT使用它array_agg(attribute_name ORDER BY attribute_name)来形成一致排序的数组以进行比较。

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

我个人会在这种方法上使用数组。

商店

您还可以使用具有空值的hstore来存储集，因为hstore可以对重复项进行重复数据删除。9.4jsonb也可以做到这一点。

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扩展提供了有用，快速的功能，可将数组视为集合。它们仅适用于整数数组，但它们确实很有用。