sql - 更新以从逗号分隔列表中删除重复项

Question

在我的 Postgres 数据库中，我有一个包含一些列的表，其中一列 B 的值如下:

A      B
a1     b0, b1, b1
a2     b2, b3
a3     b4, b4, b5, b5

我的预期输出应该是这样的:

A      B
a1     b0, b1
a2     b2, b3
a3     b4, b5

Postgres 中是否有一个简单的 UPDATE 来执行此操作？

Answer 1

具体的更新语句取决于 b 列的类型，但实际上只有 3 种不同的方式可以存储此数据，分隔字符串、文本数组或 json

逗号分隔文本字段的更新语句为:

update mytable
set b = array_to_string(array(select distinct unnest(string_to_array(b, ', '))), ', ');

如果 b 是一个文本数组，那么:

update mytable
set b = array(select distinct unnest(b));

如果 b 是一个 json 数组，则:

update mytable
set b = array_to_json(array(select distinct value from json_array_elements_text(b)));

如您所见，is case 中的最干净 语句是将数据存储为文本数组的结果。如果您必须在 1 列中存储值数组，请使用数组类型进行操作。

但是，我还建议对您的数据进行规范化处理。

上述这些语句会更新表中的所有行，因此会产生较高的执行成本。我将说明一种使用文本数组变体减少更新的方法(因为这需要最短的 sql 查询):

update mytable
set b = array(select distinct unnest(b))
where array_length(b_array, 1) != (select count(distinct c) from unnest(b) c);