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! :tada:

[muety]

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