Merge and casting in dynamic queries
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 8.7k
- Forks
- 374
- Avg merge
- 11d 16h
- Merged PRs (30d)
- 1
Description
I was attempting to verify I could do a merge query with 'postgres'
const insertedBabies = await sql`
MERGE INTO baby.babies b
USING (VALUES ${sql(babies)}) as s (baby_id, birthdate, name)
ON b.id = s.baby_id::int
WHEN NOT MATCHED THEN
INSERT (name, birthdate)
VALUES (s.name, s.birthdate)
`
and tried several variations, and came across another issue, I can't merely cast birthdate like this s (baby_id, birthdate::date, name).
Debug shows something like this
MERGE INTO baby.babies b
USING (VALUES ($1,$2,$3)) as s (baby_id, birthdate, name)
ON b.id = s.baby_id::int
WHEN NOT MATCHED THEN
INSERT (name, birthdate)
VALUES (s.name, s.birthdate)
[
{ baby_id: 68, birthdate: null, name: 'w1', role: 'admin' },
{ name: 'w2', birthdate: 2023-04-09T21:41:00.000Z },
{ name: 'w3', birthdate: 2023-04-09T21:41:00.000Z }
] [ 0, 0, 0 ]
So I guess two issues
- How do you do multiple dynamic merge in postgres
- How do you cast a certain group of columns doing an operation like this? Which I think relates to this issue https://github.com/porsager/postgres/issues/539
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 sql tagged-template example with PostgreSQL, including the VALUES list and attempted column cast, then compare it with the related issue #539. Done means establishing whether dynamic MERGE with multiple rows and casting selected columns are supported, and documenting the expected behavior or required scope.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, postgresql
- Domain
- backend, databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 28/100