Support upsert?

[ottob]

It's available in PostgreSQL 9.5: www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT

and: http://www.depesz.com/2015/05/10/waiting-for-9-5-add-support-for-insert-on-conflict-do-nothingupdate/

and it seems like something similar is available in MySQL ON DUPLICATE UPDATE

[qiangxue]

Do you mean adding an optional parameter to the Builder.Insert() method that would append the parameter to the end of the INSERT SQL?

[ottob]

I hadn't really thought about it... Just brainstorming here.

Using this test table:

create table test (
    id serial primary key,
    some_key text not null unique,
    some_val int4 not null default 0
    other_val int4 not null default 0
);
insert into test (some_key) values ('a'), ('b');

Maybe there are two use cases:

1. Common case

I want to just insert something and if it exists, update it.

db.Upsert("test", "some_key", dbx.Params{
    "some_key": "a",
    "some_val": 6,
    "other_val": 1,
}).Execute()

Maybe the "some_key" parameter can be removed and the first Param could be used. But that might be too much magic...

This would generate:

insert into test (some_key, some_val, other_val) values ('a', 6, 1)
    ON CONFLICT (some_key) DO update SET some_val = excluded.some_val, other_val = excluded.other_val
    returning *;

2. Other cases

We cannot really guess what the user want so something like your suggestion where you modify the ON CONFLICT statement directly is necessary.