Support MERGE statement · Issue #10601 · sqlalchemy/sqlalchemy

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Describe the use case

Since postgres 15, there's support for the MERGE statement, which is basically a better upsert.

Given that the extensive sqlalchemy docs on postgres describe INSERT ... ON CONFLICT in detail , but do not mention MERGE at all, it is my suspicion that this might not be supported yet, especially as postgres 15 is relatively recent.

PS. I've tried to search the issue tracker, but I couldn't find a mention of this.

Databases / Backends / Drivers targeted

Postgres

Example Use

I don't know sqlalchemy well enough to propose what the API should look like. The below is like a kid's crayon version of what things might look like.

>>> from sqlalchemy.dialects.postgresql import insert
>>> merge_stmt = insert(my_table).merge(other_table, join_predicate)\
...     .when_matched(...)\
...     .when_not_matched(...)\
>>> print(merge_stmt)
MERGE INTO my_table
USING other_table
ON join_predicate
WHEN MATCHED THEN
WHEN NOT MATCHED THEN
Some more example queries for this features can be found here.
Additional context
No response
  use case
  not really a feature or a bug; can be support for new DB features or user use cases not anticipated
  labels
      Nov 8, 2023
currently merge is not supported in any backend.

It would also not be initiated from insert since merge can do other things other than inserting rows.
There are currently no plan on supporting it, meaning that's not planned for a near release of SQLalchemy
          this is a dupe of #960 and/or #8321 so if you have plans to design and implement this API we can keep it with the original.
the two variants of "MERGE" here are:
we use MERGE to implement "ON CONFLICT" style functionality for Oracle, SQL Server, others and put it behind a generic facade Unify INSERT ... ON CONFLICT / UPSERT / MERGE functionality in single implementation #8321
we implement the full blown SQL MERGE construct note for note and have it work for those backends that support it; this still does not supply a cross-platform "ON CONFLICT" construct
IMO item 1 is vastly more useful and nobody really needs a SQL standard formalized version of MERGE