Multiple updates in one query does not support jsonb columns #765
Description
Per https://github.com/porsager/postgres#multiple-updates-in-one-query:
const users = [
[1, 'John', 34],
[2, 'Jane', 27],
]
await sql`
update users set name = update_data.name, (age = update_data.age)::int
from (values ${sql(users)}) as update_data (id, name, age)
where users.id = (update_data.id)::int
returning users.id, users.name, users.age
`change users such that name and age are in a jsonb column called info:
const users = [
[1, {name: 'John', age: 34}],
[2, {name: 'Jane', age: 27}],
]
await sql`
update users set info = update_data.info
from (values ${sql(users)}) as update_data (id, info)
where users.id = (update_data.id)::int
returning users.id, users.info
`Returns the following typescript error:
Type (number | { name: string; age: number; })[] is not assignable to type EscapableArray
Type number | { name: string; age: number; } is not assignable to type string | number
Type { name: string; age: number; } is not assignable to type string | number
edit: additionally, if one tries to force past the type error by using, e.g.:
const users = [
[1, {name: 'John', age: 34} as unknown as string],
[2, {name: 'Jane', age: 27} as unknown as string],
]then an error is returned at run-time:
PostgresError: column "info" is of type jsonb but expression is of type text