PostgreSQL消除父子表中的重复项

Question

我正在编写一些脚本来清理一些数据。由于无法复制生产数据库，我们不得不在两个表中手工加载一些测试数据，这些表具有与生产中相同的结构和相同的数据分布。

我们使用PostgreSQL 12.8

表很大，要填充这两个表需要付出很大的努力。为了加速插入，我们删除了两个表中的所有索引。在加载结束时，当想要重新创建索引时，我们发现一些数据被加载了两次。

假设我们的桌子是这样的：

  table_1:                 table_2:

     id                  id    value 
    ----               --------------
  1 id-1             1   id-1  v-1   
  2 id-1             2   id-1  v-2
  3 id-2             3   id-1  v-3
  4 id-2             4   id-1  v-4
  5 id-3             5   id-2  v-5
                     6   id-2  v-6
                     7   id-2  v-7
                     8   id-2  v-8
                     9   id-3  v-9
                    10   id-3  v-10

正如您在table_1行中看到的那样，第2行和第4行是重复的。我们想用其他的东西来代替它们，比如id-4和id-5。不管我们如何计算新的ID，它必须是唯一的。它可以是任何东西，甚至是id-1-dedup和id-2-dedup，但是在table_2中复制的is中有一半是非常重要的，才能得到相同的修复。应用修复后，我希望我们的两个表如下所示：

  table_1:                 table_2:

     id                  id    value 
    ----               --------------
  1 id-1             1   id-1  v-1   
  2 id-4             2   id-1  v-2
  3 id-2             3   id-4  v-3
  4 id-5             4   id-4  v-4
  5 id-3             5   id-2  v-5
                     6   id-2  v-6
                     7   id-5  v-7
                     8   id-5  v-8
                     9   id-3  v-9
                    10   id-3  v-10

哪些行获得了新的ID，与此无关，我们只想从table_1中消除重复项，并反映table_2中的变化。遗憾合并语句在PostgreSQL 12.8中不可用

删除数据并重新插入它实际上不是一个选项，因为填充value列需要做大量的工作，只有在没有其他聪明的方法的情况下，我们才会这样做。

提前谢谢你。

Answer 1

我将采取的方法是在这两个表中添加一个串行列。然后可以运行以下更新：

WITH cte as 
(SELECT serial_col, row_number() over(partition by id) as rn
FROM table_1)
UPDATE table_1 
SET id = table_1.id || '-dup'
FROM cte
WHERE cte.serial_col = table_1.serial_col AND cte.rn = 2;

和

WITH cte as 
(SELECT serial_col, row_number() over(partition by id) as rn
FROM table_2)
UPDATE table_2 
SET id = table_2.id || '-dup'
FROM cte
WHERE cte.serial_col = table_2.serial_col AND cte.rn > 2;

请注意，这预先假定您只是有重复的，而没有三重。

运行更新后，如果有必要，可以删除串行列。