porsager / porsager/postgres

Merge and casting in dynamic queries

Open
#584 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

invalid
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

  1. How do you do multiple dynamic merge in postgres
  2. 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

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.