如何根據行數執行條件插入？

我正在使用 Postgres 9.3，並且我需要根據表中已有的特定行數來防止插入到表中。這是表格：

                                     Table "public.team_joins"
    Column      |           Type           |                            Modifiers                             
-----------------+--------------------------+---------------------------------------------------------
id              | integer                  | not null default nextval('team_joins_id_seq'::regclass)
team_id         | integer                  | not null
Indexes:
   "team_joins_pkey" PRIMARY KEY, btree (id)
   "team_joins_team_id" btree (team_id)
Foreign-key constraints:
   "team_id_refs_teams_id" FOREIGN KEY (team_id) REFERENCES teams(id) DEFERRABLE INITIALLY DEFERRED

因此，例如，如果一個 id 為 3 的團隊只允許 20 名玩家，並且SELECT COUNT(*) FROM team_joins WHERE team_id = 3等於 20，則沒有玩家可以加入團隊 3。處理此問題並避免並發問題的最佳方法是什麼？我應該使用SERIALIZABLE事務插入，還是可以只WHERE在插入語句中使用這樣的子句？

INSERT INTO team_joins (team_id)
VALUES (3)
WHERE (
 SELECT COUNT(*) FROM team_joins WHERE team_id = 3
) < 20;

或者有沒有更好的選擇，我沒有考慮？

通常，您有一個team具有唯一team_id列的表（或類似表）。

您的 FK 約束表明：... REFERENCES teams(id)- 所以我將使用teams(id).

然後，為了避免並發寫入負載下的複雜情況（競爭條件或死鎖），通常最簡單和最便宜的方法是在父行上獲取寫入鎖，然後team在同一事務中將子行寫入team_joins( INSERT// ）。UPDATE``DELETE

BEGIN;

SELECT * FROM teams WHERE id = 3 FOR UPDATE;  -- write lock

INSERT INTO team_joins (team_id)
SELECT 3                -- inserting single row
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < 20;

COMMIT;

單行範例INSERT。_ 要一次處理一整套，你需要做的更多；見下文。

人們可能會懷疑SELECT. 如果沒有,行team_id = 3怎麼辦？條款不會WHERE取消INSERT嗎？

它不會，因為該HAVING子句使它成為整個集合的聚合，它始終只返回一行（如果給定的team_id已經有 20 行或更多行，則將其消除） -正是您想要的行為。手冊：

如果查詢包含聚合函式呼叫，但沒有GROUP BY子句，則仍然會發生分組：結果是單個組行（或者可能根本沒有行，如果單個行隨後被 消除HAVING）。如果它包含一個HAVING子句，即使沒有任何聚合函式呼叫或GROUP BY子句也是如此。

大膽強調我的。

沒有找到父行的情況也沒有問題。無論如何，您的 FK 約束都會強制執行參照完整性。如果team_id不在父表中，則無論哪種方式，事務都會因外鍵違規而終止。

所有可能競爭的寫操作team_joins都必須遵循相同的協議。

在這種UPDATE情況下，如果您更改team_id，您將鎖定源和目標團隊。

鎖在事務結束時被釋放。這個密切相關的答案中的詳細解釋：

• 如何以原子方式替換錶數據的子集

在 Postgres 9.4或更高版本中，新的、更弱的**FOR NO KEY UPDATE**可能更可取。也可以完成這項工作，減少阻塞，可能更便宜。手冊：

行為類似於FOR UPDATE，除了獲取的鎖較弱：此鎖不會阻止SELECT FOR KEY SHARE嘗試在相同行上獲取鎖的命令。這種鎖定模式也被任何UPDATE沒有獲得FOR UPDATE鎖的人獲得。

考慮升級的另一個動機……

插入同一隊的多名球員

有用的假設你有一個專欄player_id integer NOT NULL。與上述相同的鎖定，加上…

簡短的語法：

INSERT INTO team_joins (team_id, player_id)
SELECT 3, unnest('{5,7,66}'::int[])
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < (21 - 3);  -- 3 being the number of rows to insert

列表中的 set-returning 函式SELECT不符合標準 SQL，但在 Postgres 中完全有效。

只是不要SELECT在 Postgres 10 之前的列表中組合多個集合返回函式，這最終修復了那裡的一些意外行為。

更簡潔、更詳細的標準 SQL 執行相同的操作：

INSERT INTO team_joins (team_id, player_id)
SELECT team_id, player_id
FROM  (
  SELECT 3 AS team_id
  FROM   team_joins
  WHERE  team_id = 3
  HAVING count(*) < (21 - 3)
  ) t
CROSS JOIN (
  VALUES (5), (7), (66)
  ) p(player_id);

這就是全部或全部。就像在 21 點遊戲中一樣：一個太多了，整個INSERT就出局了。

功能

最後，所有這些都可以方便地封裝在一個VARIADICPL/pgSQL 函式中：

CREATE OR REPLACE FUNCTION f_add_players(team_id int, VARIADIC player_ids int[])
 RETURNS bool AS
$func$
BEGIN
  SELECT * FROM teams WHERE id = 3 FOR UPDATE;         -- lock team
-- SELECT * FROM teams WHERE id = 3 FOR NO KEY UPDATE;  -- in pg 9.4+

  INSERT INTO team_joins (team_id, player_id)
  SELECT $1, unnest($2)                                -- use $1, not team_id
  FROM   team_joins t
  WHERE  t.team_id = $1                                -- table-qualify to disambiguate
  HAVING count(*) < 21 - array_length($2, 1);
  -- HAVING count(*) < 21 - cardinality($2);           -- in pg 9.4+

  RETURN FOUND;                                        -- true if INSERT
END
$func$  LANGUAGE plpgsql;

關於FOUND.

呼叫（注意帶有值列表的簡單語法）：

SELECT f_add_players(3, 5, 7, 66);

或者，要傳遞一個實際的數組- 再次注意VARIADIC關鍵字：

SELECT f_add_players(3, VARIADIC '{5,7,66}');

有關的：

• 如何在 PostgreSQL 中使用數組作為 VARIADIC 函式的參數？
• 在單個參數中傳遞多個值

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