Sql 将重复的值更新为下一个空闲数字

发布于01月29日

我有一张表，如下所示:

CREATE TABLE "table" (
    "index" serial PRIMARY KEY,
    "number" integer
);

在这些行中:

INSERT INTO "table" ("number") VALUES
(1), (2), (2), (3), (4), (4), (4), (5), (13), (13), (17);

I want to update duplicate values to the next free integer number (except for the 1st one).
For this case it should be like:

(1), (2), (3), (4), (5), (6), (7), (8), (13), (14), (17)

这UPDATE怎么行得通呢？

推荐答案

每个下一个免费号码都可能依赖于该过程中之前的所有更新.因此，从本质上讲，这需要一个程序性的解决方案.

最佳解决方案取决于基数以及重复和间隔的频率.根据您的样品，我假设:

很少上当受骗
差距略大

下面的代码在任何情况下都可以工作，但最适合所述的假设.

DO
$do$
DECLARE
   _id int;
   _number int;
BEGIN
   CREATE TEMP TABLE free ON COMMIT DROP AS
   SELECT number
   FROM  (SELECT generate_series(min(number), max(number) + 10) FROM tbl) n(number)
   LEFT  JOIN tbl t USING (number)
   WHERE t.number IS NULL;

   -- (only) if table is big, add an index
   CREATE INDEX ON pg_temp.free (number);
   
   FOR _id, _number IN
      SELECT id, number
      FROM (
         SELECT *, lag(number) OVER (ORDER BY number) AS last_num
         FROM   tbl
         ) dup
      WHERE dup.last_num = dup.number
   LOOP
      WITH del AS (
         DELETE FROM pg_temp.free f
         USING (
            SELECT f1.number
            FROM   pg_temp.free f1
            WHERE  f1.number > _number
            ORDER  BY f1.number 
            LIMIT  1
            ) d
         WHERE f.number = d.number
         RETURNING f.number
         )
      UPDATE tbl t
      SET    number = d.number
      FROM   del d
      WHERE  t.id = _id;
   END LOOP;
END
$do$;

fiddle个

此PL/pgSQL代码块首先在给定表tbl的范围内创建一个临时的空闲数字表(free).我(任意地)在最高的一个数字之后再加10个数字.如果您可能需要超过10个以上的最高号码，您需要做更多的工作.

如果这个表很大，就创建一个索引.

然后遍历所有副本，并分配下一个空闲数字，使用它.

显然，该算法假定没有并发写入.