使用 json/jsonb 的行上的 IS DISTINCT FROM 觸發而不進行比較逐項列出？

當表具有 json 或 jsonb 列時，是否無法在 INSERT/UPDATE 觸發器中進行全行比較？我得到的錯誤讓我覺得我需要單獨比較每一列，因為該NEW.*/OLD.*格式不能用於比較 json 列。

CREATE OR REPLACE FUNCTION on_insert_update_modified()
RETURNS TRIGGER AS $$
BEGIN
 IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
   NEW.modified_at = now();
   RETURN NEW;
 ELSE
   RETURN OLD;
 END IF;
END;
$$ language 'plpgsql';

CREATE TRIGGER mytable_insert_update
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE on_insert_update_modified();

當我插入包含 json 和 jsonb 列的表時，出現以下錯誤：

ERROR:  operator does not exist: json = json
LINE 1: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
CONTEXT:  PL/pgSQL function on_insert_update_modified() line 3 at IF

有沒有更好的方法來解決這個問題？

直接問題：類型json

沒有為 Postgres 數據類型定義相等或不等運算符json。看：

• 如何查詢空對象的 json 列？
• 如何從 JSON 中刪除已知元素$$ $$PostgreSQL中的數組？

因此，涉及json列的行比較也必然會失敗 - 除了明顯的例外，請參見下文。

有（不完美的！）解決方法，但不要打擾並切換到**jsonb**，它允許這些檢查。

修復函式和触發器

您也無法比較OLDand NEWforINSERT操作，其中沒有OLD- only for UPDATEor DELETE。我建議為 使用列預設值modified_at，或者使用單獨的觸發器函式和触發器來無條件地BEFORE INSERT覆蓋任何輸入。now()

這個函式和触發器用於UPDATE：

CREATE OR REPLACE FUNCTION on_update_modified()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
BEGIN
  IF NEW IS DISTINCT FROM OLD THEN
     NEW.modified_at = now();
     RETURN NEW;
  ELSE
     RETURN NULL;  -- skip empty update, see below
  END IF;
END
$func$;

CREATE TRIGGER mytable_update  -- doesn't work!
BEFORE UPDATE ON tbl_js
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();

db<>fiddle here (Postgres 9.6)

Postgres 13 沒有什麼不同：db<>fiddle here

RETURN NULL;是可選的。如果該行根本沒有改變（並且無論如何已經執行了觸發器功能），UPDATE如果它沒有改變它，你也可以跳過該行。這節省了徒勞地編寫新行版本的可觀成本。如果其他觸發器依賴於它，或者對於其他奇特的邊緣情況，您可能仍然希望使用它。然後RETURN NEW;- 無條件地在觸發函式結束時。RETURN OLD;在我們確定兩個行值相同之後將沒有任何好處。

與列的行比較json仍然可以工作嗎？

奇怪的是，是的。從左到右處理行比較。對於表達式NEW IS DISTINCT FROM OLDPostgres 可以true在找到第一個不匹配時立即返回（在遇到問題之前json）。這可能會發生也可能不會發生，具體取決於程式碼路徑。引用手冊進行逐行比較：

筆記

如果使用較早的列解決比較問題，則可能不會發生與元素數量或類型相關的錯誤。

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