Multiple updates in one query does not support jsonb columns
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 8.7k
- Forks
- 374
- Avg merge
- 11d 16h
- Merged PRs (30d)
- 1
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
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the multiple-updates example linked in the issue with the jsonb info column, checking both TypeScript array interpolation and the generated PostgreSQL value type. Trace the array escaping and jsonb serialization paths; done means the example type-checks and updates the jsonb column without requiring an unsafe cast or producing a text-type error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- node.js, postgresql, typescript
- Domain
- backend-api-design, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100