讓 md5(NULL) 在 postgresql 中返回一個非 NULL 值

我有一個結構為 的表(content_md5 UUID, content TEXT)，我想在其中使用content_md5（即 的值md5(content)）作為主鍵，並將其用作其他表中的外鍵。

這是一個“靜態”表，其中內容（一些較大的文件）將通過它們的md5值來引用，以簡單起見，並防止表中的重複（這不會通過簡單的SERIALPKEY 給出）。

但是contentcan be NULL，它不同於在引用表中聲明不存在的內容欄位的空值。

由於在主鍵約束中不允許md5(NULL)返回NULL，並且NULL不允許，因此我希望有一種方法可以md5(NULL)返回全零而不是NULL.

例子：

-- setup
CREATE TABLE example (content_md5 UUID PRIMARY KEY, content TEXT);
CREATE TABLE test (id SERIAL PRIMARY KEY, tags TEXT, content_md5 UUID REFERENCES example(content_md5) ON DELETE RESTRICT);
INSERT INTO  example VALUES ('00000000000000000000000000000000'::uuid, NULL);

-- usage
INSERT INTO example VALUES (md5('some text')::uuid, 'some text');
INSERT INTO test (tags, content_md5) VALUES ('some content defining tags', md5('some text')::uuid);

SELECT tags, content FROM test LEFT JOIN example USING (content_md5);

-- QUESTION: Having an md5-like function to return zero-filled "md5"/uuid?
INSERT INTO example VALUES (md5(NULL)::uuid, NULL); -- ignored, because already existing record
INSERT INTO test (tags, content_md5) VALUES ('non-existing-document', md5(NULL)::uuid);

是否有可能以某種方式將返回的值轉換為一個零填充的字元串，創建一個基於md5()替換NULL為的自定義函式00000000000000000000000000000000，或者其他方式來實現這個結果？

/edit：或者我可能不需要NULL此表中的任何值，並且可以將引用的外鍵列設置NULL為達到相同的結果？

我建議這種替代設計：

-- setup
CREATE TABLE example (content_id serial PRIMARY KEY, content text);
CREATE TABLE test (id serial PRIMARY KEY, tags TEXT, content_id int REFERENCES example);

CREATE UNIQUE INDEX ON example ((md5(content)::uuid)) INCLUDE (content_id); -- !

-- usage
INSERT INTO example(content) VALUES (NULL);        -- allowed multiple times
INSERT INTO example(content) VALUES ('some text');

INSERT INTO test (tags, content_id)
SELECT 'some content defining tags', content_id
FROM   example
WHERE  md5(content)::uuid = md5('some text')::uuid;

db<>在這裡擺弄

要點

使用串列列 ( content_id) 作為表的代理 PK example- 並在任何地方作為 FK 參考。4 個字節而不是 16 個。

使用表達式上的唯一索引強制唯一性md5(example)::uuid。請注意，雜湊衝突是可能的（即使在您的表不大的情況下不太可能發生）。

在此期間，serial使用子句（Postgres 11 或更高版本）將 PK 列添加到索引中，INCLUDE以使其成為快速僅索引查找的覆蓋索引。

與 PK 列相反，這允許NULL，並且NULL不被認為是 的副本NULL，它應該涵蓋您的案例。看：

• 允許在唯一列中為空

在 Postgres 10 或更早版本中不要添加content_id到索引中。那麼你當然不會得到僅索引掃描：

CREATE UNIQUE INDEX ON example ((md5(content)::uuid));

除非您只想允許單個 NULL 實例，否則可以使用您發布的函式（引入碰撞風險 - 即使不太可能）或除上述索引之外的微小部分索引來強制執行：

CREATE UNIQUE INDEX ON example (content_id)
WHERE md5(content)::uuid IS NULL;

看：

• 使用空列創建唯一約束

根本不要將 md5 值儲存為表列（冗餘）。

---

如果您想繼續使用您在答案中發布的功能，請考慮對其進行優化：

CREATE OR REPLACE FUNCTION pg_temp.md5zero(data text)
 RETURNS uuid PARALLEL SAFE IMMUTABLE LANGUAGE sql AS
$func$
SELECT COALESCE(md5(data)::uuid, '00000000000000000000000000000000')
$func$

更快，並且可以內聯。看：

• 在 WHERE 子句中使用函式時查詢慢

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