為什麼我需要將 NULL 轉換為列類型？

我有一個助手，它正在生成一些程式碼來為我進行批量更新並生成如下所示的 SQL：

（active 和 core 欄位都是 type boolean）

UPDATE fields as t set "active" = new_values."active","core" = new_values."core"
FROM (values 
(true,NULL,3419),
(false,NULL,3420)
) as new_values("active","core","id") WHERE new_values.id = t.id;

但是它失敗了：

ERROR: column "core" is of type boolean but expression is of type text

我可以通過添加空值來讓它工作::boolean，但這看起來很奇怪，為什麼 NULL 被認為是 type TEXT？

此外，它的轉換也有點棘手，因為它需要對程式碼進行相當多的修改才能知道它應該將 NULL 轉換為什麼類型（列和值的列表目前是從一個簡單的 JSON 對像數組自動生成的） .

為什麼這是必要的，是否有更優雅的解決方案不需要生成程式碼知道 NULL 的類型？

如果相關，我將在 Node.JS 上使用sequelize來執行此操作，但在 Postgres 命令行客戶端中也得到了相同的結果。

這是一個有趣的發現。通常，NULL 沒有假定的數據類型，如您在此處看到的：

SELECT pg_typeof(NULL);

pg_typeof 
───────────
unknown

當一張VALUES桌子出現在圖片中時，情況會發生變化：

SELECT pg_typeof(core) FROM (
   VALUES (NULL)
) new_values (core);

pg_typeof 
───────────
text

此行為在https://doxygen.postgresql.org/parse__coerce_8c.html#l01373的原始碼中進行了描述：

/*
 * If all the inputs were UNKNOWN type --- ie, unknown-type literals ---
 * then resolve as type TEXT.  This situation comes up with constructs
 * like SELECT (CASE WHEN foo THEN 'bar' ELSE 'baz' END); SELECT 'foo'
 * UNION SELECT 'bar'; It might seem desirable to leave the construct's
 * output type as UNKNOWN, but that really doesn't work, because we'd
 * probably end up needing a runtime coercion from UNKNOWN to something
 * else, and we usually won't have it.  We need to coerce the unknown
 * literals while they are still literals, so a decision has to be made
 * now.
 */

（是的，PostgreSQL 原始碼在大多數地方都比較容易理解，感謝優秀的評論。）

然而，出路可能如下。假設您總是生成VALUES與給定表的所有列匹配的內容（有關其他情況，請參見下面的第二個註釋）。從您的範例中，一個小技巧可能會有所幫助：

SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);

active │ core │  id  
────────┼──────┼──────
t      │      │ 1234

在這裡，您使用強制轉換為表類型的行表達式，然後將它們提取回表中。

基於以上，你UPDATE可能看起來像

UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
          ((true, NULL, 3419)::fields),
          ((false, NULL, 3420)::fields)
    ) AS new_values(x) WHERE (x).id = t.id;

筆記：

• 我刪除了雙引號以獲得更好的人類可讀性，但您可以保留它們，因為它們有助於生成（列）名稱。
• 如果您只需要列的一個子集，您可以為此創建自定義類型。以與上面相同的方式使用它們（我使用表自動創建的類型，保存後者的行結構）。

看看在dbfiddle上工作的整個事情。

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