PostgreSQL中主鍵的重複行

假設我有一個名為 的表people，其中id是主鍵：

+-----------+---------+---------+
|  id       |  fname  |  lname  |
| (integer) | (text)  | (text)  |
+===========+=========+=========+
|  1        | Daniel  | Edwards |
|  2        | Fred    | Holt    |
|  3        | Henry   | Smith   |
+-----------+---------+---------+

我正在嘗試編寫一個足夠健壯的行重複查詢，以考慮對錶的架構更改。每當我向表中添加一列時，我都不想返回並修改重複查詢。

我知道我可以做到這一點，這將重複記錄 id 2 並為重複的記錄提供一個新的 id：

INSERT INTO people (fname, lname) SELECT fname, lname FROM people WHERE id = 2;

但是，如果我添加一age列，我需要修改查詢以考慮年齡列。

顯然我不能執行以下操作，因為它還會複製主鍵，從而導致duplicate key value violates unique constraint– 而且，我不希望它們共享相同的 id：

INSERT INTO people SELECT * FROM people WHERE id = 2

話雖如此，解決這一挑戰的合理方法是什麼？我寧願遠離儲存過程，但我不是 100% 反對它們，我想……

簡單的hstore

如果您安裝了附加模組hstore（下面連結中的說明），則有一種非常簡單的方法可以在不了解其他列的情況下替換單個欄位的值：

基本範例：使用複制行id = 2但替換2為3：

INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM people p WHERE id = 2;

細節：

• 如何使用動態 SQL 設置複合變數欄位的值
• 在 Postgres 觸發器中按鍵分配給 NEW

假設（因為它沒有在問題中定義）這people.id是一個**serial**帶有附加序列的列，您將需要序列中的下一個值。我們可以用 來確定序列名稱pg_get_serial_sequence()。細節：

• PostgreSQL 選擇主鍵為“serial”或“bigserial”

或者，如果序列名稱不會改變，您可以硬編碼序列名稱。

我們會有這樣的查詢：

INSERT INTO people
SELECT (p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text)).*
FROM people p WHERE id = 2;

哪個有效，但 Postgres 查詢計劃器存在一個弱點：表達式是針對行中的每一列單獨評估的，浪費了序列號和性能。為避免這種情況，請將表達式移動到子查詢中並僅將行分解一次：

INSERT INTO people
SELECT (p1).*
FROM  (
  SELECT p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text) AS p1
  FROM   people p WHERE id = 2
  ) sub;

一次（或幾行）可能最快。

json/jsonb

如果您沒有hstore安裝並且無法安裝其他模組，您可以使用json_populate_record()or執行類似的技巧jsonb_populate_record()，但該功能未記錄並且可能不可靠。**更新：**自 Postgres 13 起也記錄了該功能。請參閱：

• 如何使用動態 SQL 設置複合變數欄位的值

臨時臨時表

另一個簡單的解決方案是使用像這樣的臨時臨時：

BEGIN;
CREATE TEMP TABLE people_tmp ON COMMIT DROP AS
SELECT * FROM people WHERE id = 2;
UPDATE people_tmp SET id = nextval(pg_get_serial_sequence('people', 'id'));
INSERT INTO people TABLE people_tmp;
COMMIT;

我添加ON COMMIT DROP了在事務結束時自動刪除表。因此，我還將操作包裝到它自己的事務中。兩者都不是絕對必要的。

這提供了廣泛的附加選項 - 您可以在插入之前對行執行任何操作，但由於創建和刪除臨時表的成本，它會有點慢。

此解決方案同時適用於單行或任意數量的行。每行自動從序列中獲取一個新的預設值。

使用短（SQL 標準）表示法TABLE people。

動態 SQL

一次處理多行，動態 SQL 將是最快的。連接系統表pg_attribute或資訊模式中的列，並在DO語句中動態執行或編寫函式以供重複使用：

CREATE OR REPLACE FUNCTION f_row_copy(_tbl regclass, _id int, OUT row_ct int)
 LANGUAGE plpgsql AS
$func$
BEGIN
  EXECUTE (
     SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1',
                   _tbl, string_agg(quote_ident(attname), ', '))
     FROM   pg_attribute
     WHERE  attrelid = _tbl
     AND    NOT attisdropped  -- no dropped (dead) columns
     AND    attnum > 0        -- no system columns
     AND    attname <> 'id'   -- exclude id column
     )
  USING _id;

  GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
END
$func$;

稱呼：

SELECT f_row_copy('people', 9);

適用於任何具有名為 的整數列的表id。您也可以輕鬆地使列名動態化…

也許不是您的首選，因為您想這樣做stay away from stored procedures，但話又說回來，無論如何，它不是“儲存過程” …

有關的：

• psql: SELECT * … 除了一列
• 列出指定表的所有列

先進的解決方案

列是一種serial特殊情況。如果您想用各自的預設值填充更多或所有列，它會變得更加複雜。考慮這個相關的答案：

• 使用 PostgreSQL 9.3 在 CTE UPSERT 中生成預設值

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