Dlaczego muszę rzutować NULL na typ kolumny?

Mam pomocnika, który generuje dla mnie trochę kodu do wykonywania aktualizacji zbiorczych i generuje SQL, który wygląda następująco:

(Zarówno aktywne, jak i podstawowe pola są typu 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;

Jednak nie udaje się to z:

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

Mogę sprawić, że zadziała, dodając ::booleando wartości zerowych, ale to wydaje się dziwne, dlaczego NULL jest uważany za typ TEXT?

Jest to również trochę trudne do rzutowania, ponieważ wymagałoby to sporo przeróbki kodu, aby mógł wiedzieć, na jaki typ powinien rzutować wartości NULL (lista kolumn i wartości jest obecnie automatycznie generowana z prostej tablicy obiektów JSON) .

Dlaczego jest to konieczne i czy istnieje bardziej eleganckie rozwiązanie, które nie wymaga generowania kodu, aby znać typ wartości NULL?

Jeśli to istotne, używam do tego sekwencjonowania przez Node.JS, ale otrzymuję ten sam wynik w kliencie poleceń Postgres.

To interesujące odkrycie. Zwykle wartość NULL nie ma przyjętego typu danych, jak widać tutaj:

SELECT pg_typeof(NULL);

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

Zmienia się, gdy pojawia się VALUEStabela:

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

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

To zachowanie jest opisane w kodzie źródłowym na 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.
  */

(Tak, kod źródłowy PostgreSQL jest stosunkowo łatwy do zrozumienia, a większość miejsc dzięki doskonałym komentarzom.)

Wyjściem może być jednak następujące. Załóżmy, że zawsze generujesz, VALUESktóre pasują do wszystkich kolumn danej tabeli (inne przypadki znajdują się w drugiej uwadze poniżej). Na twoim przykładzie mała sztuczka może pomóc:

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

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

Tutaj używasz wyrażeń wierszy rzutowanych na typ tabeli, a następnie wyodrębniasz je z powrotem do tabeli.

W oparciu o powyższe twój UPDATEwygląd może wyglądać

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;

Uwagi:

• Usunąłem podwójne cudzysłowy dla lepszej czytelności dla ludzi, ale możesz je zachować, ponieważ pomagają przy generowaniu nazw (kolumn).
• jeśli potrzebujesz tylko podzestawu kolumn, możesz w tym celu utworzyć niestandardowe typy . Używaj ich w taki sam sposób, jak powyżej (gdzie używam typu automatycznie utworzonego z tabelą, trzymając strukturę wierszy tego ostatniego).

Spójrz na całość działającą na dbfiddle .