英文:
Golang sqlx How to do NamexExec with on conflict
问题
我正在尝试使用sqlx的NameExec方法执行一个带有冲突解决的PostgreSQL查询，但是一直收到以下错误信息：
pg: got 6 parameters but the statement requires 4
以下是示例代码：
query := `INSERT INTO table_name (name, number, address, age)
VALUES (:name, :number, :address, :age)
ON CONFLICT (name, number)
DO UPDATE SET (name, address)
= (:age, :number)`
_, err := db.NamedExec(query, records)
我知道问题出在更新部分，如果我将其移除，查询的其余部分是有效的。但是我不知道如何使其按照我期望的方式工作？
参考资料，这是使用sqlx的v1.3.5版本。
英文:
I'm trying to do an postgres query with an on conflict using sqlx NameExec but keep getting
pg: got 6 parameters but the statement requires 4
example code
query := `INSERT INTO table_name (name, number, address, age)
VALUES (:name, :number, :address, :age)
ON CONFLICT (name, number)
DO UPDATE SET (name, address)
= (:age, :number)`
_, err := db.NamedExec(query, records)
I know it's do do with the update section, and the rest of the query works if I remove it. but I don't know how to get it to work in the way that I want it to?
for reference this is using v1.3.5 of sqlx
答案1
得分: 1
正确的sqlx NameExec UPSERT语法如下：
query := `INSERT INTO table_name (name, number, address, age)
VALUES (:name, :number, :address, :age)
ON CONFLICT (name, number)
DO UPDATE SET address = excluded.address, age = excluded.age`
根据PG文档，excluded是特殊的表名。
> ...在ON CONFLICT DO UPDATE中，SET和WHERE子句可以使用表的名称（或别名）访问现有行，并使用特殊的excluded表访问插入的行。
要访问现有表的值，可以使用以下语法：
DO UPDATE SET address = table_name.address, age = table_name.age
另外，你也可以在单个事务中执行所有的upsert操作：
var query = `INSERT INTO table_name (name, number, address, age)
VALUES ($1, $2, $3, $4)
ON CONFLICT (name, number)
DO UPDATE SET address = $3, age = $4`
英文:
The correct sqlx NameExec UPSERT syntax:
query := `INSERT INTO table_name (name, number, address, age)
VALUES (:name, :number, :address, :age)
ON CONFLICT (name, number)
DO UPDATE SET address = excluded.address, age = excluded.age`
excluded is the special table name according to PG doc:
> ...The SET and WHERE clauses in ON
> CONFLICT DO UPDATE have access to the existing row using the table's
> name (or an alias), and to the row proposed for insertion using the
> special excluded table.
To access the values of existing table instead:
DO UPDATE SET address = table_name.address, age = table_name.age
Alternatively, you can do all upserts in a single transaction
var query = `INSERT INTO table_name (name, number, address, age)
VALUES ($1, $2, $3, $4)
ON CONFLICT (name, number)
DO UPDATE SET address = $3, age = $4`
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论