[Sqlalchemy-tickets] Issue #3985: Cross platform Merge statement (zzzeek/sqlalchemy)

[Михаил Д. <iss...@bi...>]

New issue 3985: Cross platform Merge statement
https://bitbucket.org/zzzeek/sqlalchemy/issues/3985/cross-platform-merge-statement

Михаил Доронин:

I would like to provide cross-platform Merge statement. I've this this [issue](https://bitbucket.org/zzzeek/sqlalchemy/issues/960/implement-merge-and-or-pg-on-conflict-and)

I think there should be both dialect specific variants as well as the lowest common denominator between them. The reasons are

* It's in sql 2003 standard and many databases support actual standard (okay, maybe they not so popular for sqlalchemy users)
* It is now possible to implement lowest common denominator implementation in mysql, sqlite  (maybe not that performant, but who uses sqlite for performance? And user can be warned about that in docs) and postgresql.
* I believe that lowest common denominator is the actual most useful (and used) use case. Which is "insert values or update existing ones". Kind of like `{**d1, **d2}` in python3.

```
/* postgresql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON CONFLICT (a) DO UPDATE SET dname = EXCLUDED.dname;

/* mysql */
INSERT INTO table (a, b)
    VALUES (1, 2), (2, 3)
    ON DUPLICATE KEY a=VALUES(a) UPDATE

/* sqlite */
INSERT OR REPLACE INTO tabel (a, b) 
    VALUES
    (1, COALESCE((SELECT role FROM Employee WHERE id = 1), 2)),
    (2, COALESCE((SELECT role FROM Employee WHERE id = 2), 3))
```
possible sqlalchemy interface `insert(table, [(1, 2), (2, 3)]).merge(table.c.a)`

I would be very happy to provide implementation if you can provide guidance on how to do that. I'm trying to understand your code, but aside from understanding that there is `Insert` class in `sql/dlm.py` and there are probably implementations of it in all dialects I haven't been able to figure out much.

Maybe you can show how to make that for postgres since it already has `on_conflict` in dialect. And then I can use that as example to add this to sqlite, sqlalchemy and maybe other dialects.

Responsible: zzzeek