Drizzle Team•11mo ago

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));

2 Replies

Mykhailo•11mo ago

Hello, @lukeabove! You can cast js array to postgres array like this:

const arr = [1, 2, 3];

const postgresArray = `{${arr.join(',')}}`;

const arr = [1, 2, 3];

const postgresArray = `{${arr.join(',')}}`;

lukeaboveOP•11mo ago

Hey, @solo thank you very much! Worked like a charm!

Gaming

Programming

Could not determine data type error while batch update with sql