douliu3831 2015-07-30 20:50
浏览 418
已采纳

PostgreSQL参数问题$ 1

I am working on setting up a database, building a custom Upsert as Postgresql apparently doesn't have that yet. Anyway my parameters aren't playing nicely.

I am using Martini.

This code:

func CreateBook(ren render.Render, r *http.Request, db *sql.DB) {
    _, err := db.Query("INSERT INTO books (title, first, last, class) SELECT $1, $2, $3, $4 WHERE NOT EXISTS (SELECT * FROM books WHERE title = $1)",
    r.FormValue("title"),
    r.FormValue("first"),
    r.FormValue("last"),
    r.FormValue("class"))

    PanicIf(err)

Throws this error:

pq: inconsistent types deduced for parameter $1

I am fairly certain it's some kind of typecasting issue with the second $1 but none of the rational solutions seem to make sense.

Its a stupid question with hopefully an easy answer but I haven't been able to find any answers anywhere else.

  • 写回答

1条回答 默认 最新

  • dri98076 2015-07-30 21:43
    关注

    It is hard to tell exactly what is going on as the database structure is not known. But trying this query in the sqlfiddle shows the following:

    create table books (
      id serial,
      title varchar
    );
    
    PREPARE booksplan AS
      INSERT INTO books (title)
      SELECT $1 WHERE NOT EXISTS (SELECT * FROM books WHERE title = $1);
    
    >> ERROR:  inconsistent types deduced for parameter $1
    >> Detail: text versus character varying Position: 59
    

    So I suspect that when the $1 is used for the first time, the text is deduced but the varchar is deduced for the second $1 (as it compared with title, which is varchar).

    As workaround you can probably try

    _, err := db.Query(`INSERT INTO books (title, first, last, class)
        SELECT CAST($1 AS VARCHAR), $2, $3, $4
        WHERE NOT EXISTS (SELECT 1 FROM books WHERE title = $1)`,
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题