Feature Request: Provide InsertIgnore and InsertIgnoreMany

[evanmcclure]

Hi. We love the library. It's super simple and very powerful.

We'd love to see functionality for ignoring duplicate keys when inserting. Can you provide alternatives for Insert and InsertMany that provide INSERT IGNORE functionality?

You can find more information here:

• MariaDB/MySQL: INSERT IGNORE - https://mariadb.com/kb/en/library/insert-ignore/
• PostgreSQL: ON CONFLICT DO NOTHING - https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT

Thank you,

Evan Sunnyvale, CA

[AlekSi]

Can you tell me more about your use case?

Will something like this work for you?

package reform

// CheckDuplicate returns nil if err is nil or duplicate key error.
// It returns err unmodified otherwise.
func CheckDuplicate(err error) error {
  …
}

if err := q.Insert(str); reform.CheckDuplicate(err) != nil {
  …
}

[evanmcclure]

Nice! Thank you, that will work nicely for INSERT (q.Insert()).

How about for multi-value inserts? (q.InsertMulti()). When I tried this in MariaDB, the entire INSERT is rejected if one of the value lists fails due to a conflict.

[dgellow]

What about something similar to what we currently have for querier.Select methods:

tail := "on conflict do nothing"
querier.Insert(record, tail)

Though that would work for postgres, but not with @evanmcclure's issue with the INSERT IGNORE when using MariaDB, so maybe a way to specify a prefix and a suffix?

I found two ways to deal with this with the current implementation with postgres: check the error class, or construct the query and directly call querier.Exec.

For reference, that's how you can do something similar to the ON CONFLICT DO NOTHING by checking the postgres error (simplified and incomplete version of the CheckDuplicate @AlekSi suggested):

	err = querier.Insert(record)
	pgErr, ok := err.(*pq.Error)
	if !ok || pgErr.Code.Class() != "23" { // 23 stands for "Class 23 — Integrity Constraint Violation" in postgres, see https://www.postgresql.org/docs/9.3/errcodes-appendix.html
		return err
	}

[dgellow]

Hmm, my solution isn't really one, because the conflict isn't handled on the SQL side any existing transaction is invalidated.

Edit: that's also valid for @AlekSi's suggestion using the CheckDuplicate.