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
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
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.