porsager / porsager/postgres

values helper not working as expected

Open
#1,042 0 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
JavaScript
Stars
8.7k
Forks
374
Avg merge
11d 16h
Merged PRs (30d)
1

Description

I was trying to get something like the following MERGE statement running:

const rows = [
  { id: 1, name: "Ana" },
  { id: 2, name: "Peter" },
]

// use VALUES in fragment to trigger values helper
const valuesFragment = sql`VALUES ${sql(rows, 'id', 'name')}`

sql`
MERGE INTO "table"
USING ( ${valuesFragment} ) AS "values" ( "id", "name" )
...
`

But this ended up throwing the UNDEFINED_VALUE, 'Undefined values are not allowed error.

After some digging i found the reason to be that the first argument of types.js:valuesBuilder() is an array of array:

[
  [
    { id: 1, name: "Ana" },
    { id: 2, name: "Peter" },
  ]
]

so accessing the columns value for 'id' and 'name' failed because the row is an array and not the row object itself.

I think the issue for this in types.js:131 where multi is calculated from first[0] instead of just first, like in the insert helper.
After switching to just first it worked for my VALUES statement but i am not sure if i broke somehing else.

Is my assumption correct or am i using the values helper incorrectly?

Happy to submit a fix if this is actually a bug :)

Edit

Yes, i broke something. Using arrays of primitive values no longer works (like in: id IN ${psql([1, 2])}. Ended up changing the builder for in to wrap it's first parameter sent to the values builder always as a nested array.

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 in types.js:131 at valuesBuilder(), then compare how the insert and in helpers shape their first argument. Reproduce both the object-row VALUES example and the primitive array example; done means VALUES handles row objects without breaking expressions such as id IN ${psql([1, 2])}.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, postgres
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.