I've got a helper that's generating some code to do bulk updates for me and generates SQL that looks like this:
(Both the active and core fields are of 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;
However it fails with:
ERROR: column "core" is of type boolean but expression is of type text
I can get it to work by adding ::boolean
to the nulls, but that just seems odd, why is NULL considered of type TEXT
?
Also it's a bit tricky to cast because it would require quite a bit of a rework of the code for it to know what type it should cast NULLs to
(the list of columns and values is currently being autogenerated from a simple array of JSON objects).
Why is this necessary and is there a more elegant solution that doesn't require the generating code to know the type of NULLs?
If it's relevant, I'm using sequelize over Node.JS to do this, but am also getting the same result in the Postgres command line client.
Best Answer
This is an interesting finding. Normally, a NULL has no assumed data type, as you can see here:
This changes when a
VALUES
table comes into the picture:This behaviour is described in the source code at https://doxygen.postgresql.org/parse__coerce_8c.html#l01373:
(Yes, PostgreSQL source code is relatively easy to understand and most places, thanks to excellent comments.)
The way out, however, might be the following. Let's say you are always generating
VALUES
that match all columns of a given table (see the second note below for other cases). From your example, a small trick could possibly help:Here you use row expressions casted to the table's type, and then extracting them back to a table.
Based on the above, your
UPDATE
could look likeNotes:
Look at the whole thing working on dbfiddle.