英文:
Postgresql Parameter Issue $1
问题
我正在设置一个数据库，并构建一个自定义的Upsert，因为Postgresql显然还没有这个功能。不过，我的参数似乎不太好用。
我正在使用Martini。
这段代码：
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)
}
抛出了这个错误：
pq: inconsistent types deduced for parameter $1
我相当确定这是第二个$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
得分: 30
很难确定具体发生了什么，因为数据库结构未知。但在sqlfiddle中尝试这个查询会显示以下内容：
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
所以我怀疑当第一次使用$1时，推断出的是文本类型，但第二个$1推断出的是varchar类型（因为它与varchar类型的title进行比较）。
作为解决方法，你可以尝试以下代码：
_, 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)`,
希望对你有帮助！
英文:
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)`,
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论