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.