MERGE queries shouldn't require primary key setting.

[sblackstone]

Describe the feature

Merge (upsert) queries currently require all of the columns in clause.Conflict.Columns to be primary keys, otherwise it silently falls back to a regular insert.

A table can have a primary key thats simply an auto-increment referencing the row, but also unique constraints which can trigger the ON CONFLICT case of a merge query.

Motivation

Upsert queries that aren't necessarily looking at the primary key.

	MERGE table1 AS [Target] USING (
	SELECT
	uniq_field1 = ?,
	uniq_field2   = ?,
	value = ?)
	AS [Source] ON [Target].uniq_field1 = [Source].uniq_field1 and [Target].uniq_field2 = [Source].uniq_field2
	WHEN MATCHED THEN
	UPDATE
		SET
		[Target].value=[Source].value,
		WHEN NOT MATCHED THEN
		INSERT (
			uniq_field1,
			uniq_field2,
			value)
	VALUES
		(
			[Source].uniq_field1,
			[Source].uniq_field2,
			[Source].value
		)

Related Issues

None.

[sblackstone]

Additionally, the fall-back behavior when an on-conflict clause exists but doesn't match the primary keys was super-confusing and I'd suggest that it should return an error rather than just doing an insert.

[jhajjaarap]

Any good news on this issue?

[sblackstone]

Would be nice to have @jhajjaarap's patch upstreamed! 🎉

[muety]

@jhajjaarap Perhaps create a pull request for upstreaming your fix? Would love to get that merged.

[huseyinkabil]

I also have trouble with this topic :(

Model is:

type Model struct {
	ID                int64     `gorm:"primaryKey;column:ID"`
        AnyUniqueColumn string `gorm:"uniqueIndex;column:AnyUniqueColumn"`
}

Query is:

result := db.Model(&Model{}).
		Clauses(clause.OnConflict{
			Columns: []clause.Column{{Name: "AnyUniqueColumn"}},
			DoNothing: true,
		}).
		CreateInBatches(listOfObjects, 100)

In this case, I expect driver builds "MERGE INTO" sql. But driver builds "INSERT INTO" sql. As a result I got "duplicate key" error.

@jinzhu What do you think about this issue? Will there be any updates? 🙏

@ddadaal Can you create a pull request for your fix? 🙏