Gorm: Support Postgres new upsert feature 'ON CONFLICT'

[ezeql]

www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT

[goddardcm]

In the interest of bumping this item, it should be noted that Postgres 9.5 was released today.

[derekperkins]

Is this supported on MySQL for ON DUPLICATE UPDATE?

[vpol]

Is the feature postgresql upsert feature supported? If yes, can we get examples?

[calebthompson]

The feature is technically supported, but not in a great way. The lines from @jinzhu's change, https://github.com/jinzhu/gorm/commit/c9dfd80959ef31311810835abeaaf5d07131089c#diff-04c6e90faac2675aa89e2176d2eec7d8R265, are true in that the sql produced is correct, but you'd need to extend "ON CONFLICT" there to specify the behavior you wanted, such as "ON CONFLICT (id) UPDATE SET name = $1, address = $2". Then, it'd only work if we can rely on the 'interpolation' of those variables into the SQL.

I needed upserts, and ended up writing the insert sql myself and using Exec. It also means batch upsert is out of the question; I have it running in a loop for each record.

[calebthompson]

I wasn't sure Postgres supported upserting multiple rows at once, so I looked into it. Looks like you can use the EXCLUDED alias in the DO UPDATE clause, so this is an option:

db.Set("gorm:insert_option", "ON CONFLICT (name) DO UPDATE SET code = EXCLUDED.code").Create(&products)
// INSERT INTO products (name, code)
//   VALUES
//     ('name', 'code'),
//     ('another name', 'another code')
// ON CONFLICT (name) DO UPDATE SET
//     code = EXCLUDED.code;

[EIrwin]

@calebthompson, @jinzhu using your example, I attempted both upserting multiple rows at once, as well as one at a time and executing raw SQL.

Attempting to upsert multiple at once looked like the following:

    db.Set("gorm:insert_option", "ON CONFLICT (user_id, tenant_id, date) DO UPDATE SET completion = EXCLUDED.completion,total_activities = EXCLUDED.total_activities").Create(&rows)

This results in the following error:

panic: reflect: call of reflect.Value.Interface on zero Value

goroutine 245 [running]:
reflect.valueInterface(0x0, 0x0, 0x0, 0xc4203e9801, 0xc4203e98f0, 0x0)
    /usr/local/Cellar/go/1.8.3/libexec/src/reflect/value.go:930 +0x1fa
reflect.Value.Interface(0x0, 0x0, 0x0, 0x0, 0x1)
    /usr/local/Cellar/go/1.8.3/libexec/src/reflect/value.go:925 +0x44
github.com/commercialtribe/etl/vendor/github.com/jinzhu/gorm.createCallback(0xc420362200)
    /Users/eirwin/Source/Repos/go/src/github.com/eirwin/etl/vendor/github.com/jinzhu/gorm/callback_create.go:68 +0x2ee
github.com/eirwin/etl/vendor/github.com/jinzhu/gorm.(*Scope).callCallbacks(0xc420362200, 0xc420163100, 0x9, 0x10, 0x12)
    /Users/eirwin/Source/Repos/go/src/github.com/eirwin/etl/vendor/github.com/jinzhu/gorm/scope.go:821 +0x4c
github.com/eirwin/etl/vendor/github.com/jinzhu/gorm.(*DB).Create(0xc4203b2090, 0x1425580, 0xc4202acbe0, 0x143fba0)
    /Users/eirwin/Source/Repos/go/src/github.com/eirwin/etl/vendor/github.com/jinzhu/gorm/main.go:407 +0x86

The attempt at upserting single record at a time looks like the following:

for _, row := range rows {
   l.db.Set("gorm:insert_option", "ON CONFLICT (user_id, tenant_id, date) DO UPDATE SET completion = excluded.completion,total_activities = excluded.total_activities").Create(&row)
}

This results in the following error

(pq: column excluded.completion does not exist)

Any ideas what could be causing this, or how I can fix it?

I would prefer to be able to accomplish upsert of multiple rows.

[jwaldrip]

@jinzhu, any guidance here?

[dc0d]

Any updates?

If gorm would not going to support this (for example because maybe it's a non-standard SQL statement, or other databases have no equivalent), please state it explicitly.

[ruggertech]

?

[delfer]

UPSERT wanted

[KellyLSB]

I wasn't sure Postgres supported upserting multiple rows at once, so I looked into it. Looks like you can use the EXCLUDED alias in the DO UPDATE clause, so this is an option:

db.Set("gorm:insert_option", "ON CONFLICT (name) DO UPDATE SET code = EXCLUDED.code").Create(&products)
// INSERT INTO products (name, code)
//   VALUES
//     ('name', 'code'),
//     ('another name', 'another code')
// ON CONFLICT (name) DO UPDATE SET
//     code = EXCLUDED.code;

For the Set Values:
https://github.com/jinzhu/gorm/blob/712c4655605f094d283047501ae613db9c798850/callback_create.go#L60-L79
https://github.com/jinzhu/gorm/blob/712c4655605f094d283047501ae613db9c798850/callback_create.go#L115-L123

[KellyLSB]

var fields []string
for _, field := range tx.NewScope(Session{}).Fields() {
    if field.Name != "AccountID" && field.Name != "InvoiceID" {
        if field.IsNormal && !field.IsIgnored {
            fields = append(fields, fmt.Sprintf(
                "%v = excluded.%v", field.DBName, field.DBName,
            ))
        }
    }
}

var session = &Session{
    ID:         utils.EnsureUUID(s.Get("sessionUUID")),
    RemoteAddr: &r.RemoteAddr,
    UserAgent:  utils.StringPtr(r.UserAgent()),
    Referer:    utils.StringPtr(r.Referer()),
    Method:     &r.Method,
    URL:        utils.StringPtr(r.URL.String()),
}

tx.Preload("Account", "Invoice").Set(
    "gorm:insert_option", fmt.Sprintf(
        "ON CONFLICT (id) DO UPDATE SET %s", strings.Join(fields, ", "),
    ),
).Create(session)

s.Set("sessionUUID", session.ID)

[Djolivald]

@KellyLSB Nice trick and this will do for now, but that'd be nice to have this feature in the ORM directly