从postgres的字符串数组列中删除重复条目

Question

我有一个PostgreSQL表，其中有一列包含字符串数组。该行具有一些唯一的数组字符串或者一些重复的字符串。如果存在，我想从每行中删除重复的字符串。

我尝试过一些查询，但没能实现。

以下是表格：

  veh_id |             vehicle_types              
 --------+----------------------------------------
      1  | {"byd_tang","volt","viper","laferrari"} 
      2  | {"volt","viper"}                        
      3  | {"byd_tang","sonata","jaguarxf"}        
      4  | {"swift","teslax","mirai"}              
      5  | {"volt","viper"}                        
      6  | {"viper","ferrariff","bmwi8","viper"}   
      7  | {"ferrariff","viper","viper","volt"}    

我期望得到以下输出：

  veh_id |             vehicle_types              
 --------+----------------------------------------
      1  | {"byd_tang","volt","viper","laferrari"} 
      2  | {"volt","viper"}                        
      3  | {"byd_tang","sonata","jaguarxf"}        
      4  | {"swift","teslax","mirai"}              
      5  | {"volt","viper"}                        
      6  | {"viper","ferrariff","bmwi8"}           
      7  | {"ferrariff","viper","volt"}            

- amol desai

Answer 1

由于每行的数组是独立的，使用带有ARRAY构造函数的简单相关子查询即可完成任务:

SELECT *, ARRAY(SELECT DISTINCT unnest (vehicle_types)) AS vehicle_types_uni
FROM   vehicle;

请参见：

• 为什么array_agg()比非聚合ARRAY()构造器慢？

请注意，NULL会转换为空数组('{}')。我们需要对其进行特殊处理，但在下面的UPDATE中它被排除了。

快速简单。但是不要使用这个。虽然你没有说，但通常你会想要保留数组元素的原始顺序。你的基本示例也暗示了这一点。在相关子查询中使用WITH ORDINALITY，这会变得更加复杂:

SELECT *, ARRAY (SELECT v
                 FROM   unnest(vehicle_types) WITH ORDINALITY t(v,ord)
                 GROUP  BY 1
                 ORDER  BY min(ord)
                ) AS vehicle_types_uni
FROM   vehicle;

参见：

• PostgreSQL unnest() 函数及其元素编号

UPDATE 实际上是用于删除重复项的：

UPDATE vehicle
SET    vehicle_types = ARRAY (
                 SELECT v
                 FROM   unnest(vehicle_types) WITH ORDINALITY t(v,ord)
                 GROUP  BY 1
                 ORDER  BY min(ord)
                )
WHERE  cardinality(vehicle_types) > 1  -- optional
AND    vehicle_types <> ARRAY (
                 SELECT v
                 FROM   unnest(vehicle_types) WITH ORDINALITY t(v,ord)
                 GROUP  BY 1
                 ORDER  BY min(ord)
                ); -- suppress empty updates (optional)

两个添加的WHERE条件都是可选的，以提高性能。第一个条件完全是多余的。每个条件还排除了NULL情况。第二个条件抑制了所有空更新。

请参见:

• 我如何在多个列上选择DISTINCT？

如果您尝试在不保留原始顺序的情况下执行此操作，很可能会更新大多数行而没有必要，只是因为元素的顺序或更改而没有重复项。

需要Postgres 9.4或更高版本。

db<>fiddle 这里

- Erwin Brandstetter

Answer 2

我不保证这是高效的，但类似这样的代码可能会起作用：

with expanded as (
  select veh_id, unnest (vehicle_types) as vehicle_type
  from vehicles
)
select veh_id, array_agg (distinct vehicle_type)
from expanded
group by veh_id

如果你真的想要做一些最坏情况下是O(n)的高级操作，你可以编写一个自定义函数：

create or replace function unique_array(input_array text[])
returns text[] as $$
DECLARE
  output_array text[];
  i integer;
BEGIN

  output_array = array[]::text[];

  for i in 1..cardinality(input_array) loop
    if not (input_array[i] = any (output_array)) then
      output_array := output_array || input_array[i];
    end if;
  end loop;

  return output_array;
END;
$$
language plpgsql

使用示例：

select veh_id, unique_array(vehicle_types)
from vehicles

- Hambone

---

为什么要建议这样一个糟糕的函数实现？使用一个简单的 language sql 函数，该函数在输入数组上使用 unnest 和 distinct，会更加高效。 - user330315