Multiple updates in one query -> not working "operator does not exist: integer = text"
Nobody has claimed this yet.
- 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
- 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 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