Gorm: Getting "sql: no rows in result set" error when using gorm:insert_option

[andreychernih]

Running the following code:

        if err := db.Set("gorm:insert_option", "ON CONFLICT (twitter_user_id, twitter_follower_id) DO NOTHING").Create(&f).Error; err != nil {
            return err
        }

Produces this error:

(sql: no rows in result set)
2017/09/23 10:32:06 Error: sql: no rows in result set

What version of Go are you using (go version)?

go version go1.8 darwin/amd64

Which database and its version are you using?

PostgreSQL 9.6.2

[tfmertz]

I had the same issue and I found a way around this by using the FirstOrCreate method. I haven't tested it with objects that don't have ids though.

From docs:

// Unfound
db.FirstOrCreate(&user, User{Name: "non_existing"})
//// INSERT INTO "users" (name) VALUES ("non_existing");
//// user -> User{Id: 112, Name: "non_existing"}

// Found
db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)
//// user -> User{Id: 111, Name: "Jinzhu"}

[xorcare]

You can update one or more fields to correctly work out RETURNING and gorm understood that everything worked successfully.

INSERT  INTO "links" ("id","link_id","created_at","updated_at","deleted_at") VALUES ('bedc4fa9-c36f-4ba6-8a5a-8f66cd86959f','e6658c63-a70b-4bd7-bae3-f20cbdc0edb2','2019-06-07 09:36:40','2019-06-07 09:36:40',NULL) ON CONFLICT (id, link_id) DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING "links"."id"

type Link struct {
    ID        uuid.UUID `gorm:"primary_key"`
    LinkID    uuid.UUID `gorm:"primary_key"`
    CreatedAt time.Time
    UpdatedAt time.Time
}

db.LogMode(true)

db = db.Set(
    "gorm:insert_option",
    "ON CONFLICT (id, link_id) DO UPDATE SET updated_at = EXCLUDED.updated_at",
)

err = db.Create(&Link{ID: uuid.New(), LinkID: uuid.New()}).Error

if err != nil {
    log.Fatal("create", err)
}

err = db.Create(&Link{
    ID:     uuid.MustParse("bffcab30-2ffc-4a0b-8166-c7626c1400c6"),
    LinkID: uuid.MustParse("ce3107ba-afd7-417d-8b37-ee9dd08e7dea"),
}).Error
if err != nil {
    log.Fatal("create 2", err)
}

var count int
if err := db.Model(&Link{}).Count(&count).Error; err != nil {
    log.Fatal("count", err)
}

log.Println("Count of ", count)
os.Exit(0)

```sql
create table links
(
id uuid not null,
link_id uuid not null,
created_at timestamp with time zone default now() not null,
updated_at timestamp with time zone default now() not null,
constraint links_pk
primary key (id, link_id)
);

### Env

go version go1.12.4 linux/amd64
PostgreSQL 11.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

### Log
```text
2019/06/07 09:34:19 Count of  2
2019/06/07 09:34:43 Count of  3
2019/06/07 09:34:51 Count of  4