构建Server函数报错：operator does not exist: uuid = character varying

核心错误原因 #

你SQL语句里的CAST语法完全写错了，正确的类型转换格式是CAST(列名 AS 数据类型)，而不是CAST 列名 = CAST(...)，这种语法错误导致PostgreSQL无法正确解析类型转换逻辑，最终触发uuid与字符类型不匹配的报错。

修正后的完整代码 #

app.get('/unsigned-leases/:tenantId', async (req, res) => {
  const { tenantId } = req.params;

  console.log("tenantid", tenantId);

  const uuidTenantId = tenantId ? tenantId : null;

  try {
    const [results] = await sequelize.query(
      `SELECT * FROM "Files"
       WHERE id = ANY (
         SELECT unnest("leaseDocs")
         FROM "Tenants"
         WHERE "user_id" = CAST(:tenantId AS uuid)
       )
       AND signed = false;`,
      {
        replacements: { tenantId: uuidTenantId },
        type: sequelize.QueryTypes.SELECT
      }
    );

    res.status(200).json(results);
  } catch (error) {
    console.error('Error fetching unsigned leases:', error);
    res.status(500).json({ error: 'Failed to fetch unsigned leases.' });
  }
});

额外优化建议 #

• 如果Tenants表的user_id字段本身就是uuid类型，可以简化转换逻辑，让Sequelize自动处理参数类型匹配：

  // 修改SQL语句和参数配置
  const [results] = await sequelize.query(
    `SELECT * FROM "Files"
     WHERE id = ANY (
       SELECT unnest("leaseDocs")
       FROM "Tenants"
       WHERE "user_id" = :tenantId
     )
     AND signed = false;`,
    {
      replacements: { tenantId: uuidTenantId },
      type: sequelize.QueryTypes.SELECT,
      bind: { tenantId: { value: uuidTenantId, type: sequelize.UUID } }
    }
  );
  

• 可以在接口开头添加UUID格式校验，提前拦截无效参数：

  // 需先安装validator包：npm install validator
  const { isUUID } = require('validator');
  if (tenantId && !isUUID(tenantId)) {
    return res.status(400).json({ error: 'Invalid tenantId format.' });
  }
  

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