Cross platform Merge statement

[Михаил Доронин]

I would like to provide cross-platform Merge statement. I've seen this issue

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 table (a, b) 
    VALUES
    (1, COALESCE((SELECT role FROM Employee WHERE id = 1), 2)),
    (2, COALESCE((SELECT role FROM Employee WHERE id = 2), 3))


/* merge from wikipedia */
MERGE INTO table USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

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.

[Mike Bayer]

hello there:

I would like to provide cross-platform Merge statement. I've seen this issue

yes, that's this one. this is a dupe. Feel free to reopen (though as of yet I don't see a change to the resolution as yet)

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.

for SQLite, we implement INSERT OR REPLACE and that's it. All that coalesce stuff is already supported. That all goes into that recipe / end user "merge" thing. Now I don't have to warn anybody about anything.

I think there should be both dialect specific variants

yes. The Postgresql ON CONFLICT construct, which was provided by a contributor, should serve as the model for similar constructs in MySQL and others

as well as the lowest common denominator between them.

IMO not really. It is very easy for end-users to build recipes and/or third party libraries that provide some kind of "merge()" / "upsert()" method that delegates to the dialect-specific versions in the way that they want. I'd rather not maintain this as there is too much variety / unknown between the constructs. Will gladly include example recipes.

It's in sql 2003 standard and many databases support actual standard

that's the MERGE statement, which does all kinds of crazy use cases that the Postgresql, MySQL, and SQLite examples I'm seeing here do not. SQL Server and Oracle support it. I'll gladly support a PR of similar quality with MERGE in it, tests can be part of the dialect suite tests since this is SQL standard, we'd mark #960 as fixed. The reason nobody wants it is that the cultures surrounding SQL Server and Oracle are generally a lot more conservative about edge-case features like these.

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.

I don't want to be responsible for the subtle differences in behavior between these constructs, so we can implement the syntaxes sure, but rolling them together in a one-size-fits-all, very easy as a recipe for now (using techniques that were not available when #960) was opened.

So you need to send PRs for: MySQL on duplicate key update and SQLite INSERT or REPLACE. (and MERGE if you really cared). Note that these are complicated features that have a lot of code quality and testing requirements so they'd be pulled into the gerrit and it would likely take a lot of iterations before they were in good shape, not to mention my own time to get around to reviewing and fixing things up.

thanks!

[Mike Bayer]

Duplicate of #960.