使用squirrel+echo操作PostgreSQL时遇pq语法错误：syntax error at or near ","

使用squirrel和echo包开发后端服务向PostgreSQL添加用户时，发送以下JSON格式POST请求：

{
  "user_name": "IronMan",
  "first_name": "Tony",
  "last_name": "Stark",
  "email": "ironman@marvel.com",
  "user_status": "I",
  "department": "Comics"
 }

持续收到PostgreSQL错误：pq: syntax error at or near ","

注：数据库表中user_id为自动生成。

相关代码如下：

func CreateUser(c echo.Context) error {
    newUser := new(models.User)
    c.Bind(&newUser)

    //dbTableName := os.Getenv("DB_TABLE_NAME")

    sqlStatement, _, err := sq.Insert(dbTableName).
        Columns("user_name", "first_name", "last_name", "email", "user_status", "department").
        Values(newUser.User_Name, newUser.First_Name, newUser.Last_Name, newUser.Email,
            newUser.User_Status, newUser.Department).
        ToSql()
    if err != nil {
        c.Logger().Panicf("Unable to create query: %s \n%s", sqlStatement, err)
    }

    row := db.DB.QueryRow(sqlStatement)
    err = row.Scan(newUser.USER_ID, newUser.User_Name, newUser.First_Name, newUser.Last_Name,
        newUser.Email, newUser.User_Status, newUser.Department)
    if err != nil {
        c.Logger().Panicf("Unable to scan results query:\n%s\n%s", err, row)
    }
    //defer sqlStatement.Close()

    return c.JSON(http.StatusCreated, newUser)
}

错误提示指向逗号附近的语法错误，已列出所有列，不清楚问题原因。

1. 修复表名未赋值的问题 #

注释掉dbTableName的赋值语句会导致生成的SQL中表名为空，这是引发逗号语法错误的直接原因。取消注释该语句，确保表名正确读取：

dbTableName := os.Getenv("DB_TABLE_NAME")

2. 添加RETURNING子句获取自动生成的user_id #

PostgreSQL中要获取自动生成的主键，必须在Insert语句后添加RETURNING user_id，否则执行Insert后无结果返回，Scan操作会失败。通过squirrel的Suffix方法添加该子句：

sqlStatement, _, err := sq.Insert(dbTableName).
    Columns("user_name", "first_name", "last_name", "email", "user_status", "department").
    Values(newUser.User_Name, newUser.First_Name, newUser.Last_Name, newUser.Email,
        newUser.User_Status, newUser.Department).
    Suffix("RETURNING user_id"). // 添加返回主键的子句
    ToSql()

3. 使用参数化查询规避语法风险和SQL注入 #

直接生成带具体值的SQL容易因特殊字符引发语法错误，还存在SQL注入风险。建议使用squirrel的RunWith方法直接绑定数据库执行查询，无需手动生成SQL：

// 替换原ToSql和QueryRow逻辑
row := sq.Insert(dbTableName).
    Columns("user_name", "first_name", "last_name", "email", "user_status", "department").
    Values(newUser.User_Name, newUser.First_Name, newUser.Last_Name, newUser.Email,
        newUser.User_Status, newUser.Department).
    Suffix("RETURNING user_id").
    RunWith(db.DB).
    QueryRow()

4. 修正Scan的参数传递方式 #

Scan需要接收指针才能修改变量值，且只需扫描返回的user_id（其他字段已从请求绑定获取）：

err = row.Scan(&newUser.USER_ID)

完整修复后的代码 #

func CreateUser(c echo.Context) error {
    newUser := new(models.User)
    if err := c.Bind(&newUser); err != nil {
        return c.JSON(http.StatusBadRequest, map[string]string{"error": "Invalid request body"})
    }

    dbTableName := os.Getenv("DB_TABLE_NAME")
    if dbTableName == "" {
        return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Database table name not configured"})
    }

    row := sq.Insert(dbTableName).
        Columns("user_name", "first_name", "last_name", "email", "user_status", "department").
        Values(newUser.User_Name, newUser.First_Name, newUser.Last_Name, newUser.Email,
            newUser.User_Status, newUser.Department).
        Suffix("RETURNING user_id").
        RunWith(db.DB).
        QueryRow()

    err := row.Scan(&newUser.USER_ID)
    if err != nil {
        c.Logger().Errorf("Failed to create user: %v", err)
        return c.JSON(http.StatusInternalServerError, map[string]string{"error": "Failed to create user"})
    }

    return c.JSON(http.StatusCreated, newUser)
}

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