Problem with the Postgresql $ 1 parameter

Question

I am working on setting up a database by creating a custom Upsert, since Postgresql does not seem to have this yet. In any case, my options do not play well.

I use 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 pretty sure that this is some kind of problem with casting to the second $ 1, but none of the rational solutions make sense.

His stupid question, hopefully an easy answer, but I could not find the answers anywhere.

Answer 1

It is hard to say exactly what is happening because the structure of the database is unknown. But trying this query in 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 

Therefore, I suspect that when $ 1 is used for the first time, the text is displayed, but varchar is displayed for the second $ 1 (since it compares with the header, which is varchar).

As a 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)`,