Types in array spread are not infered, e.g. in multiple update

[technic]

I have a query like this

/* 
@name example
@params vals -> ((b, id)...)
*/
UPDATE table SET bar = v.bar FROM (VALUES :vals) as v(b, id) WHERE table.id = v.id

and pg asks me to cast v.id manually to integer, because pgtyped thinks id is a string.

[x-EricH-x]

Just faced a very similar issue, it is partially due to how postgresql infer types in a value expression. ref: https://dba.stackexchange.com/a/228058

basically postgresql will interpret all values in a value expression to TEXT unless a type is explicitly defined.

using values in an insert is a special case, because postgresql can get the type from the inserting table.

probably as an improvement pgtyped can interpret type info in the magic comment like @params vals -> ((b::TEXT, id::INTEGER)...), but right now I think the only way to make it work is explicitly stating the type in SQL.

[adelsz]

This will be tracked in the meta issue. For now explicit type-casting is the only solution.