时间戳

首页 > 解决方案 > 连续插入遇到的三个 ID 的所有唯一组合

arrays - 连续插入遇到的三个 ID 的所有唯一组合

问题描述

我想用三个不同 ID 遇到的所有唯一组合不断更新一个表。这些 ID 可以是产品/组/区域 ID 等。我已经在这里抽象出来了。我想通过调用来更新这个表create_combinations_if_needed('{{1, 10, 100}, {2, 11, 101}}')，这将创建组合{1, 10, 100}，{1, 10, 100}如果它们不存在的话。

这是我在下面做的尝试。

CREATE TABLE combinations (
    id serial PRIMARY KEY,
    some_id1 integer NOT NULL,
    some_id2 integer NOT NULL,
    some_id3 integer NOT NULL
);
CREATE UNIQUE INDEX ON combinations(some_id1, some_id2, some_id3);

CREATE OR REPLACE function create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
   INSERT INTO combinations (some_id1, some_id2, some_id3)
   SELECT some_id1, some_id2, some_id3
   FROM UNNEST(p_combinations) AS comb(some_id1, some_id3, some_id3)
   ON CONFLICT (some_id1, some_id2, some_id3)
   DO NOTHING
   RETURNING TRUE;
$$;

但是，如果我尝试创建此函数，则会收到以下错误：

ERROR:  table "comb" has 1 columns available but 3 columns specified
CONTEXT:  SQL function "create_combinations_if_needed"

我究竟做错了什么？

标签： arrayspostgresql

解决方案

正如@a_horse_with_no_name 已经说明的那样： unnest() 函数不仅可以展平第一个维度，还可以展平所有嵌套元素。所以它为每个整数创建一行。这当然会导致一列（在您的情况下）有六个值。这就是异常消息的含义：您生成了一列，但预期为三列。

因此，您需要一个解决方案来仅取消嵌套第一个维度。我正在使用此处介绍的解决方案：

演示：db<>小提琴

创建 Lukas 函数：

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
  RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_subscripts($1,1) d1
    ,  generate_subscripts($1,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;

这个仅取消嵌套第一个维度。所以你可以在你的函数中使用它而不是你的 unnest() 尝试：

CREATE OR REPLACE function create_combinations_if_needed(p_combinations integer[][]) RETURNS boolean
LANGUAGE sql AS
$$
   INSERT INTO combinations (some_id1, some_id2, some_id3)
   SELECT unnest[1], unnest[2], unnest[3]
   FROM unnest_2d_1d(p_combinations) as unnest
   ON CONFLICT (some_id1, some_id2, some_id3)
   DO NOTHING
   RETURNING TRUE;
$$;

推荐阅读