使用GORM执行原生UPDATE查询时遇类型不匹配错误求助

问题分析 #

你遇到的"update_at" is of type bigint but expression is of type text错误，核心原因是PostgreSQL无法自动推断VALUES子句中update_at字段的正确类型，默认将其识别为text类型，与表中bigint类型的update_at字段不兼容。虽然你怀疑是GORM的写法问题，但本质是原生SQL的类型推断漏洞，GORM的参数处理逻辑也可能放大这个问题。

解决方法 #

方法1：显式指定VALUES子句中字段的类型 #

在定义临时表c时，明确声明每个字段的类型，让PostgreSQL直接识别正确类型，避免隐式转换错误：

UPDATE 
  public.openedu_user_tokens AS t 
SET 
  update_at = c.update_at, 
  delete_at = c.delete_at, 
  user_id = c.user_id, 
  org_id = c.org_id, 
  email = c.email, 
  token = c.token, 
  event = c.event, 
  expiry_time = c.expiry_time, 
  send_email = c.send_email, 
  verify_date = c.verify_date, 
  redirect_url = c.redirect_url 
FROM 
  (
    VALUES 
      (
        $1::bigint, $2::bigint, $3::bigint, $4::bigint, $5::bigint, $6::text, $7::text, $8::text, 
        $9::timestamp, $10::boolean, $11::timestamp, $12::text
      ), 
      (
        $13::bigint, $14::bigint, $15::bigint, $16::bigint, $17::bigint, $18::text, $19::text, $20::text, 
        $21::timestamp, $22::boolean, $23::timestamp, $24::text
      )
  ) AS c(
    id bigint, update_at bigint, delete_at bigint, user_id bigint, 
    org_id bigint, email text, token text, event text, expiry_time timestamp, 
    send_email boolean, verify_date timestamp, redirect_url text
  ) 
WHERE 
  t.id = c.id;

注意：需根据你的表字段实际类型调整类型声明，比如如果update_at是timestamp类型，就改成$2::timestamp，确保与表字段类型完全一致。

方法2：用GORM的原生SQL执行时正确传递参数 #

使用GORM的Exec方法执行原生SQL，确保参数按顺序正确传递，避免GORM对SQL结构进行不必要改写：

sql := `UPDATE 
  public.openedu_user_tokens AS t 
SET 
  update_at = c.update_at, 
  delete_at = c.delete_at, 
  user_id = c.user_id, 
  org_id = c.org_id, 
  email = c.email, 
  token = c.token, 
  event = c.event, 
  expiry_time = c.expiry_time, 
  send_email = c.send_email, 
  verify_date = c.verify_date, 
  redirect_url = c.redirect_url 
FROM 
  (
    VALUES 
      ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12), 
      ($13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24)
  ) AS c(
    id, update_at, delete_at, user_id, 
    org_id, email, token, event, expiry_time, 
    send_email, verify_date, redirect_url
  ) 
WHERE 
  t.id = c.id`

// 按顺序传递所有参数，确保参数类型与表字段匹配
result := db.Exec(sql, param1, param2, param3, ..., param24)

这种方式下GORM仅替换占位符，不会修改SQL结构，只要参数类型正确，就能避免类型不匹配问题。

方法3：使用GORM内置的批量更新API #

如果不想手写原生SQL，可以用GORM的批量更新功能，让框架自动生成正确的SQL：

// 定义要更新的记录切片
tokens := []OpeneduUserToken{
  {ID: 1, UpdateAt: 1690000000, DeleteAt: 0, UserID: 100, ...},
  {ID: 2, UpdateAt: 1690001000, DeleteAt: 0, UserID: 101, ...},
}

// 批量更新，GORM会生成高效的批量更新语句
db.Model(&OpeneduUserToken{}).Updates(tokens)

这种方式完全规避了手写原生SQL的类型问题，GORM会根据模型定义自动处理字段类型和SQL生成。

内容的提问来源于stack exchange，提问作者Anoencs