postgresSQL这种情况插入数据无法返回主键id #1031

New Issue

lunny · 2018-07-01T10:06:47Z

bruinxs commented

2018-07-01 10:06:47 +00:00

(Migrated from github.com)

postgresSQL数据库，若表同时有自增主键字段和一个'bigserial'类型字段，插入数据时不会返回主键id。例如：

表结构：

type Item struct {
	Id     int64 `xorm:"pk autoincr"`
	Serial int64 `xorm:"bigserial"`
	Name   string
}

插入一天数据：

item := &Item{Name: "not return id"}
_, err = engine.Insert(item)
if err != nil {
	fmt.Println("error: ", err)
	return
}
bys, _ := json.Marshal(item)
fmt.Println(string(bys))    //打印：{"Id":0,"Serial":4,"Name":"not return id"}

sql日志如下：

[xorm] [info]  2018/07/01 17:58:27.790632 [SQL] BEGIN TRANSACTION
[xorm] [info]  2018/07/01 17:58:27.804622 [SQL] CREATE TABLE IF NOT EXISTS "mall_item" ("id" BIGSERIAL PRIMARY KEY  NOT NULL, "serial" BIGSERIAL NOT NULL, "name" VARCHAR(255) NULL)
[xorm] [info]  2018/07/01 17:58:27.805623 [SQL] COMMIT
[xorm] [info]  2018/07/01 17:58:27.805623 [SQL] INSERT INTO "mall_item" ("name") VALUES ($1) RETURNING "serial" []interface {}{"not return id"}

日志打印显示的是RETURNING "serial"，但如果将Id字段放到Serial字段下面，就会正确返回id数据

type Item struct {
	Serial int64 `xorm:"bigserial"`
	Id     int64 `xorm:"pk autoincr"`
	Name   string
}

[xorm] [info]  2018/07/01 18:03:29.323712 [SQL] INSERT INTO "mall_item" ("name") VALUES ($1) RETURNING "id" []interface {}{"can return id"}
{"Serial":0,"Id":5,"Name":"can return id"}

所以在有自增主键的情况下，能否优先返回主键的值。结构体字段顺序的改变不应该影响返回结果。

postgresSQL数据库，若表同时有自增主键字段和一个'bigserial'类型字段，插入数据时不会返回主键id。例如：表结构： ```go type Item struct { Id int64 `xorm:"pk autoincr"` Serial int64 `xorm:"bigserial"` Name string } ``` 插入一天数据： ```go item := &Item{Name: "not return id"} _, err = engine.Insert(item) if err != nil { fmt.Println("error: ", err) return } bys, _ := json.Marshal(item) fmt.Println(string(bys)) //打印：{"Id":0,"Serial":4,"Name":"not return id"} ``` sql日志如下： ``` [xorm] [info] 2018/07/01 17:58:27.790632 [SQL] BEGIN TRANSACTION [xorm] [info] 2018/07/01 17:58:27.804622 [SQL] CREATE TABLE IF NOT EXISTS "mall_item" ("id" BIGSERIAL PRIMARY KEY NOT NULL, "serial" BIGSERIAL NOT NULL, "name" VARCHAR(255) NULL) [xorm] [info] 2018/07/01 17:58:27.805623 [SQL] COMMIT [xorm] [info] 2018/07/01 17:58:27.805623 [SQL] INSERT INTO "mall_item" ("name") VALUES ($1) RETURNING "serial" []interface {}{"not return id"} ``` 日志打印显示的是`RETURNING "serial"`，但如果将`Id`字段放到`Serial`字段下面，就会正确返回id数据 ```go type Item struct { Serial int64 `xorm:"bigserial"` Id int64 `xorm:"pk autoincr"` Name string } ``` ``` [xorm] [info] 2018/07/01 18:03:29.323712 [SQL] INSERT INTO "mall_item" ("name") VALUES ($1) RETURNING "id" []interface {}{"can return id"} {"Serial":0,"Id":5,"Name":"can return id"} ``` 所以在有自增主键的情况下，能否优先返回主键的值。结构体字段顺序的改变不应该影响返回结果。

lunny added

and removed

labels 2020-03-04 02:23:53 +00:00

lunny added this to the (deleted) milestone 2020-03-04 02:23:57 +00:00

lunny removed this from the (deleted) milestone 2023-07-26 01:23:28 +00:00

Sign in to join this conversation.