Postgresql Parameter Issue $1

Question

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.

Gorton

Are you willing to rewrite this with a fifth parameter instead of reusing $1? – Acceleration

Sure but the goal is to try to verify that the html input doesn't represent a duplicate entry (hence the title). – Gorton

Answer 1

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

Conservancy

FYI: CAST() is also pronounced "::" in PostgreSQL, as in SELECT $1::VARCHAR, $2, ... – Padron

Still useful in 2022! Thanks, mate. – Sestertium