為什麼我的 UNIQUE 約束沒有觸發？
我有這個
UNIQUE約束：ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8");然後我做：
INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true); INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);兩者都有效。兩行被添加到表中。第二個在邏輯上應該失敗。但事實並非如此。
我究竟做錯了什麼？這讓我發瘋。
注意：如果這是我自己的數據，我將擁有一個真正獨特的列，而不是這個“瘋狂”的
UNIQUE約束。問題是這個表保存了我銀行賬戶中的記錄，而且他們愚蠢地在他們的 CSV 轉儲中沒有一個真正的“唯一”列，我可以用它來實際確保沒有插入重複的行，所以我有想出一個組合整個表中的所有列來確定唯一性的方法。
NULL是罪魁禍首，就像gwaigh 解釋的那樣。解決方案？
您需要所有列來使行唯一嗎？通常，只組合幾個就足夠了。銀行數據應該有很多非空列…
要使其在包含單個可空列的情況下工作，您可以使用此處概述的部分索引：
但是，如果有多個可為空的列，這很快就會變得不切實際。
對於多個可為空的列，一個簡單的解決方案是一個唯一的表達式索引，
COALESCE例如：CREATE UNIQUE INDEX bank_uni_idx ON bank (col1, col2, COALESCE(col3, ''), col4, col5, col6, COALESCE(col7, ''), col8);假設
col3&col7是可為空的字元串類型列，其中空字元串 ('') 和NULL是可互換的。顯然，同樣可以用於單個可為空的列。
您需要一個
NULL不會與其他合法值（在我的範例中為空字元串）衝突的安全替換。到目前為止所有解決方案（包括您的原始解決方案）的缺點是這麼多列上的大索引。可以讓它相當昂貴。這使我得到了我真正想給出的答案：
高效的解決方案
根據行的廉價且足夠唯一的雜湊值創建
UNIQUE索引或約束（簡化為定義列）。Postgres 14
帶有用於記錄（包括匿名記錄！）的內置雜湊函式，這比我下面的自定義函式便宜得多。
hash_record_extended(record, bigint) --> bigint看：
hashtextextended()它與（詳情見下文）屬於同一系列功能。現在，表達式索引似乎比生成的列更有吸引力。所以就：CREATE UNIQUE INDEX bank_hash_uni ON bank (hash_record_extended((col1, col2, col3, col4, col5, col6, col7, col8),0));就這樣。以下大部分內容仍然適用。
Postgres 13（原始答案）
將雜湊值儲存在生成的列中並在其
UNIQUE上創建約束。看：假設所有
text列。CREATE OR REPLACE FUNCTION public.f_bank_bighash(col1 text, col2 text, col3 text, col4 text , col5 text, col6 text, col7 text, col8 text) RETURNS bigint LANGUAGE sql IMMUTABLE COST 25 PARALLEL SAFE AS 'SELECT hashtextextended(textin(record_out(($1,$2,$3,$4,$5,$6,$7,$8))), 0)'; COMMENT ON FUNCTION public.f_bank_bighash(text, text, text, text, text, text, text, text) IS 'Fast, practically unique signature for the set of defining columns in table bank. IMMUTABLE for use in index. "record_out"() is only stable, but with only text input it is effectively immutable.'; ALTER TABLE bank ADD COLUMN bank_bighash bigint NOT NULL GENERATED ALWAYS AS (public.f_bank_bighash(col1, col2, col3, col4, col5, col6, col7, col8)) STORED -- appends column in last position , ADD CONSTRAINT bank_bighash_uni UNIQUE (bank_bighash);db<>在這裡擺弄
與
NULL價值觀一起工作。需要Postgres 12或更高版本，其中添加了擴展雜湊函式和生成的列。
hashtextextended()以及hastext()用於散列分區或散列索引的快速可靠散列的內部函式。它們是無證的。但他們不會消失。正如Tom Lane 所指出的，它們在不同的硬體平台上可能不穩定。在將數據庫集群從小端系統移動到大端系統後重新創建雜湊（如果應該發生類似的事情）。
for 的第二個參數
hashtextextended()是雜湊的鹽。使用任何bigint常量，只要確保在任何地方都使用相同的常量。堅持下去0，除非你知道得更好。此外，雖然使用巨大的 bigint 密鑰空間極不可能發生雜湊衝突，但理論上的可能性始終存在。如果發生這種情況，您將獲得兩個不同行的唯一違規。如果對此感到不舒服，請改用
md5()並存uuid儲值。看：16 個字節，
uuid而不是 8 個字節bigint。計算、儲存和比較的成本更高一些。從理論上講，碰撞仍然是可能的，但你必須偏執。較舊的（或任何）版本可以對
hashtext()返回做同樣的事情integer。使碰撞的可能性更大。仍然不太可能達到幾千個條目。並使用觸發器使雜湊列保持最新，或者使用表達式上的唯一索引而不是生成列上的約束。
雜湊衝突的機率？
**TL;DR：**非常安全，最多幾百萬行。
您可以使用“生日問題”的數學公式計算實際機率。假設一個完美的雜湊函式，一個 bigint 雜湊 (
2^64 - 1，四捨五入到2^64不同的值) 的數字是：SELECT sqrt(2^65 * ln(1/(1 - 0.1)))::int AS p10 -- 1971577271 , sqrt(2^65 * ln(1/(1 - 0.01)))::int AS p1 -- 608926881 , sqrt(2^65 * ln(1/(1 - 0.001)))::int AS p01 -- 192124822 , sqrt(2^65 * ln(1/(1 - 0.0001)))::int AS p001 -- 60741529 , sqrt(2^65 * ln(1/(1 - 0.00001)))::int AS p0001 -- 19207726 , sqrt(2^65 * ln(1/(1 - 0.000001)))::int AS p00001 -- 6074003讀取最後的計算
p00001：大約有 600 萬個條目，至少單個雜湊衝突的機率低於 0.000001 (= 0.0001 %)。
IOW，當應用於一百萬個表，每個表有 6M 行時，我們可以預期單個表會遇到雜湊衝突。
對於大約 6 億個條目 (
p1)，至少單個雜湊衝突的機率為 0.01。使用 16 字節鍵空間（不同值）計算
md5()/ ：uuid``2^128SELECT sqrt(2^129 * ln(1/(1 - 0.000001)))::int8 AS p00001 -- 26087642172564964閱讀：
在大約 26 萬億行中，發生碰撞的機率變為 0.000001。
您的問題源於 NULL 值。
來自文件（強調添加）
通常，如果表中有不止一行，其中包含在約束中的所有列的值都相等，則違反了唯一約束。**但是，在此比較中，兩個空值永遠不會被視為相等。**這意味著即使存在唯一約束，也可以在至少一個受約束的列中儲存包含空值的重複行。此行為符合 SQL 標準，但我們聽說其他 SQL 數據庫可能不遵循此規則。因此，在開發旨在可移植的應用程序時要小心。