lukeabove Posts - Answer Overflow

lukeabove

•Created by lukeabove on 2/18/2024 in #help

Could not determine data type error while batch update with sql

I'm trying to batch update using sql function with set, but I'm getting an error below. Updating non-array values work. I believe it has to do with the way I pass the array to the sql chunk - I really do not know how I could possible pass it in a different way. I've also find some resources that I've got to cast the data, but that did not work.

// drizzle log
Query: update "Chapters" set "tags" = (case when id = $1 then ARRAY[($2, $3, $4)] end) where "Chapters"."id" in ($5) -- params: [13201, "tags", "tags", "tags", 13201]

// error message
Error generating podcast translation: PostgresError: could not determine data type of parameter $2
    at ErrorResponse (/Users/-------/CODE/MY_PROJECTS/SCRIPT_CAST/be-script-cast/node_modules/postgres/src/connection.js:790:26)
  

//schema.js
export const chapters = pgTable('Chapters', {
  // ..
  tags: varchar('tags', { length: 255 }).array(),
  //..
});

// persist translation 
const mockSuccessfullyTranslatedChapters = [
  {
   //.
    tags: ['tags', 'tags', 'tags'],
    qa: [
      {
        question: 'question',
        answer: 'answer',
      },
    ],
  },
// ...
    ],
  },
];

const tagsSqlChunks = [];
// other chunks
const ids = [];

for (const chapter of mockSuccessfullyTranslatedChapters) {
  tagsSqlChunks.push(sql`when id = ${chapter.id} then ARRAY[${chapter.tags}]`);
// other chunks.push ...
  ids.push(chapter.id);
}
const tagsCaseStatement = sql.join([sql`(case`].concat(tagsSqlChunks).concat(sql`end)`), sql.raw(' '));

const result = await db.update(chapters)
  .set({
    tags: tagsCaseStatement,
    // ..
  })
  .where(inArray(chapters.id, ids));

// drizzle log
Query: update "Chapters" set "tags" = (case when id = $1 then ARRAY[($2, $3, $4)] end) where "Chapters"."id" in ($5) -- params: [13201, "tags", "tags", "tags", 13201]

// error message
Error generating podcast translation: PostgresError: could not determine data type of parameter $2
    at ErrorResponse (/Users/-------/CODE/MY_PROJECTS/SCRIPT_CAST/be-script-cast/node_modules/postgres/src/connection.js:790:26)
  

//schema.js
export const chapters = pgTable('Chapters', {
  // ..
  tags: varchar('tags', { length: 255 }).array(),
  //..
});

// persist translation 
const mockSuccessfullyTranslatedChapters = [
  {
   //.
    tags: ['tags', 'tags', 'tags'],
    qa: [
      {
        question: 'question',
        answer: 'answer',
      },
    ],
  },
// ...
    ],
  },
];

const tagsSqlChunks = [];
// other chunks
const ids = [];

for (const chapter of mockSuccessfullyTranslatedChapters) {
  tagsSqlChunks.push(sql`when id = ${chapter.id} then ARRAY[${chapter.tags}]`);
// other chunks.push ...
  ids.push(chapter.id);
}
const tagsCaseStatement = sql.join([sql`(case`].concat(tagsSqlChunks).concat(sql`end)`), sql.raw(' '));

const result = await db.update(chapters)
  .set({
    tags: tagsCaseStatement,
    // ..
  })
  .where(inArray(chapters.id, ids));

3 replies

Gaming

Programming