使用外鍵“合併”Postgres 表中的兩行

我正在使用 PostgreSQL 中的以下兩個表來保存我讀過的書籍的數據庫：

CREATE TABLE authors (
   id SERIAL PRIMARY KEY,
   name text
);

CREATE TABLE books (
   id SERIAL PRIMARY KEY,
   title text,
   author_id integer REFERENCES authors(id) ON UPDATE CASCADE ON DELETE CASCADE,
   UNIQUE(title, author_id)
);

現在，在瀏覽我的作者列表時，我發現了以下兩個條目：

id | name
----------
1 | Mark Twain
2 | Samuel Clemens

我想做的是刪除“馬克吐溫”條目，並有效地更新所有引用“馬克吐溫”的書籍以引用“塞繆爾克萊門斯”。我知道我可以手動執行此操作，但我想要一個有效的解決方案，無論哪些表引用authors(id)

我想過這樣做（在事務中）：

1. 將Mark Twain 更改id為 2，讓我們UPDATE CASCADE負責更改引用。
2. 刪除馬克吐溫條目

但這會遇到一些問題，主要是：

1. 第一步創建一個重複的主鍵
2. 一旦它們都具有相同的 ID，我不確定如何引用要刪除的正確行！
3. DELETE CASCADE第二步讓我擔心

還有一個更微妙的問題，可以用我的（管理不善的）books表格的一部分來說明：

id | title              | author_id
------------------------------------
1 | "Huckleberry Finn" | 1
2 | "Huckleberry Finn" | 2

在這裡，即使我的兩步過程成功，我也會UNIQUE違反books.

有沒有辦法做到這一點，並解決大多數/所有這些問題？使用 Postgres 9.4。

假設您只想books在合併重複作者後刪除重複項。

BEGIN;
LOCK books, authors;

CREATE TEMP TABLE dupes ON COMMIT DROP AS (SELECT 2 AS dupe, 1 AS org);

DELETE FROM books b  -- delete duplicate books
USING  dupes d
WHERE  b.author_id = d.dupe
AND    EXISTS (
  SELECT 1
  FROM   books
  WHERE  title = b.title
  AND    author_id = d.org
  );

UPDATE books b  -- now we relink all remaining books
SET    author_id = d.org
FROM   dupes d   
WHERE  b.author_id = d.dupe;

DELETE FROM authors a  -- now we can delete all dupes
USING  dupes d
WHERE  a.id = d.dupe;

COMMIT;

臨時表可以保存許多行以一次刪除許多欺騙。

對每個引用的表重複前兩個步驟authors.id。如果有很多我會動態創建和執行語句……

我明確鎖定表以避免並髮乾擾。

自動化

一個基本函式可能如下所示：

CREATE OR REPLACE FUNCTION f_remove_dupe(_tbl text, _col text, _dupe int, _org int)
 RETURNS void  AS
$func$
DECLARE
  _ftbl text;
  _fcol text;
BEGIN
  FOR _ftbl, _fcol IN
     -- table and column name behind all referencing FKs
     SELECT c.conrelid::regclass::text, f.attname
     FROM   pg_attribute  a 
     JOIN   pg_constraint c ON a.attrelid = c.confrelid AND a.attnum = c.confkey[1]
     JOIN   pg_attribute  f ON f.attrelid = c.conrelid AND f.attnum = c.conkey[1]
     WHERE  a.attrelid = _tbl::regclass
     AND    a.attname  = _col
     AND    c.contype  = 'f'
  LOOP
     EXIT WHEN _ftbl IS NULL;  -- skip if not found
     EXECUTE format('
        UPDATE %1$s
        SET    %2$I = $2
        WHERE  %2$I = $1'
      , _ftbl, _fcol)
     USING _dupe, _org;
  END LOOP;

  EXECUTE format('
     DELETE FROM %I WHERE %I = $1'
   , _tbl, _col)
  USING _dupe;
END
$func$ LANGUAGE plpgsql;

稱呼：

SELECT f_remove_dupe('authors', 'id', 2, 1);

這個簡單的版本…

• …僅適用於單個騙子。
• …忽略UNIQUE引用表中的約束。
• …假設所有 FK 約束僅使用一列，忽略多列 FK
• …忽略來自並發事務的可能干擾。

適應您的要求。

有關的：

• 查找外鍵約束的引用欄位
• 無法執行動態 DDL，參數為 NULL
• Postgres 函式中的 SQL 注入與準備好的查詢

引用自：https://dba.stackexchange.com/questions/122306