porsager / porsager/postgres

Multiple updates in one query -> not working "operator does not exist: integer = text"

Open
#539 11 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

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

Description

const users = [
    [ 1, 'John' ],
    [ 2, 'Jane' ],
  ]

  console.log(await sql`
      update users
      set email = update_data.email
      from (values ${sql(users)}) as update_data (id, email)
      where users.id = update_data.id
  `.describe())
create table public.users
(
    id                         integer      default nextval('users_id_seq'::regclass) not null primary key,
    email                      varchar(255) default ''::character varying             not null,
);

Lead to :

PostgresError: operator does not exist: integer = text
    at ErrorResponse (/Users/alexisviscogliosi/dev/restore-staging/node_modules/postgres/cjs/src/connection.js:768:26)
    at handle (/Users/alexisviscogliosi/dev/restore-staging/node_modules/postgres/cjs/src/connection.js:471:6)
    at Socket.data (/Users/alexisviscogliosi/dev/restore-staging/node_modules/postgres/cjs/src/connection.js:312:9)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at new Query (/Users/alexisviscogliosi/dev/restore-staging/node_modules/postgres/cjs/src/query.js:35:9)
    at sql (/Users/alexisviscogliosi/dev/restore-staging/node_modules/postgres/cjs/src/index.js:111:11)
    at transformValue (/Users/alexisviscogliosi/dev/restore-staging/src/transformers/helpers.ts:20:24)
    at Object.emailTransformer [as email] (/Users/alexisviscogliosi/dev/restore-staging/src/transformers/email.ts:8:24)
    at obfuscate (/Users/alexisviscogliosi/dev/restore-staging/src/index.ts:29:43)
    at Object.<anonymous> (/Users/alexisviscogliosi/dev/restore-staging/src/index.ts:40:1)
    at Module._compile (node:internal/modules/cjs/loader:1218:14)
    at Module.m._compile (/Users/alexisviscogliosi/dev/restore-staging/node_modules/ts-node/src/index.ts:1618:23)
    at Module._extensions..js (node:internal/modules/cjs/loader:1272:10)
    at Object.require.extensions.<computed> [as .ts] (/Users/alexisviscogliosi/dev/restore-staging/node_modules/ts-node/src/index.ts:1621:12) {
  query: '\n' +
    '  update users set email = update_data.email\n' +
    '  from (values ($1,$2),($3,$4)) as update_data (id, email)\n' +
    '  where users.id = update_data.id\n',
  parameters: [ 1, 'John', 2, 'Jane' ],
  args: [ Builder { first: [Array], rest: [] } ],
  types: [ 0, 0, 0, 0 ]
}

I don't really understand, I just grab the example from the readme, I am doing something wrong ?
The query works well in postgres even with parameterized parameter.

Edit:
Doing

const users = [
    [ 1, 'John' ],
    [ 2, 'Jane' ],
  ]

  console.log(await sql`
      update public.users
      set email = update_data.email
      from (values ${sql(users)}) as update_data(id, email)
      where 1 = users.id 
  `.describe())

Seems to unlock the situation so the problem must be on your side sinde postgres can handle the query with the update_data.id

Edit:
I don't see any tests for this feature Multiple updates in one query

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 with the README's “Multiple updates in one query” example and reproduce the reported SQL using the users table definition and values shown here. Compare the generated query, parameters, and types in the error report with the working PostgreSQL form; done means the example executes successfully and has a regression test, since the issue notes that tests are currently missing.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, postgresql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.