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

[alexisvisco]

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

[alexisvisco]

Edit casting to ::int works, maybe you should update your example ?

[AlexMayleRdn]

Running into this as well.

[bonesoul]

same issue here.

@alexisvisco where should i make the ::int cast?

[alexisvisco]

same issue here.

@alexisvisco where should i make the ::int cast?

In my case update_data.id::int

[AlexMayleRdn]

Yeah casting has been working fine, but it has been a real pain. I think this really is a library issue though. Anytime you do ... (values ${sql(...)} ... it sends the parameters with type text. Postgres infers the type to use according to its rules [1], which usually does not end up being what you want when the incoming type is text.

In other contexts the appropriate types are sent over for integers, floats, dates, etc, why does that not occur when using the helper in a values list context?

[1] https://www.postgresql.org/docs/14/typeconv-union-case.html

[porsager]

There's actually a PR to remove that from regular parameters as well #392 . This library aims to be as close to PostgreSQL as possible (this is not an ORM)™️. Now there are plenty of ways to solve the issues faced here, but I'm not at my computer these days, so unless someone else pitches in before I'm back, I'll be sure to get back then 😊

[AlexMayleRdn]

Okay I will take the convo over there. The crux of this problem then becomes: we just need an interface to cast INSIDE of the values list, next to each literal. Right now it is agonizing to cast in sub-query select lists, join conditions, return lists, etc. I've had to perform a lot of gymnastics to get bulk inserts with joins to work.

[porsager]

Yeah, if you have any opinions related to that PR, sure, but try to keep in mind we're trying to stay as close to the DB as possible to let it do the inference it does directly because it's better at casting according to its types than any js layer, and also to avoid another abstraction.

If you don't mind, I'd like to see actual code of what you're trying to do to get a better understanding.

[AlexMayleRdn]

Upon more thought I think I see what you mean: once you remove any implicit casting the library does, postgres' type inference should be just fine. It's just problematic right now because the library is casting to text automatically in this situation.

But here's a bulk insert example that should have been simple but got really verbose. I'm inserting from a select statement that is mostly comprised of a values list, but does also have one join.

I had to wrap the values list in its own sub-query so I could have the chance to cast every column. Without that, postgres complained that a bunch of the types were text which did not match the corresponding types for table_1. Maybe there's an easier way to write this but I couldn't come to it.

    INSERT INTO table_1 (
            equipmentinstanceid,
            systemid,
            currentvalue,
            storeroomsid,
            inima,
            isdead,
            timestamp
    ) SELECT
            s.equipmentinstanceid::int,
            s.systemid::int,
            s.currentvalue::real,
            s.storeroomsid::int,
            s.inima::boolean,
            s.isdead::boolean,
            s.timestamp::timestamptz
    FROM productinstances pi
    JOIN (
            SELECT
                static.equipmentinstanceid,
                static.systemid,
                static.currentvalue,
                static.storeroomsid,
                static.inima,
                static.isdead,
                static.timestamp
            FROM (VALUES ${conn(rows)}) as static (
                equipmentinstanceid,
                systemid,
                currentvalue,
                storeroomsid,
                inima,
                isdead,
                timestamp
            )
    ) as s ON pi.productinstanceid = s.equipmentinstanceid::int
    WHERE pi.tracked = TRUE
    RETURNING equipmentledger.equipmentinstanceid

[evelant]

I think this could possibly also be causing a problem when issuing an update that contains jsonb columns with array values. For example, an update sql`update my_table set ${sql(updates)} where id=${some_id} where one of the columns is jsonb and the value for that column is [false, false, false, false, false] results in PostgresError: column "my_column" is of type jsonb but expression is of type boolean

[danmana]

I had a similar issue recently while trying to update some embeddings (id, vector).

I used jsonb_to_recordset to solve it, as there is only one value to bind (to jsonb) it works fine.
This only works when the array has JSON serializable values.
I haven't checked if it has any performance impact, seemed to work fast in my case.

// We need an array of objects to work
const users = [
    { id: 1, email: 'John' },
    { id: 2, email: 'Jane' },
  ]

  console.log(await sql`
      update users
      set email = update_data.email
      from  jsonb_to_recordset(${users}) as update_data (id int, email text)
      where users.id = update_data.id
  `.describe())

My embeddings example

const embeddings = [
  { id: 1, embedding: [1.0,...] } // float[]
];

await sql`
    update my_table as t
    set
      embedding = e.embedding
    from
      jsonb_to_recordset(${embeddings}) as e (id int, embedding vector)
    where
      t.id = e.id
`;