Repository navigation
Unify INSERT ... ON CONFLICT / UPSERT / MERGE functionality in single implementation #8321
Description
The current support for insert on conflict requires the use of an insert() function from the specific dialect package.
This is inconvenient since it's make it hard to port the code from one dialect to another.
Since the functionality is similar we could have a single implementation and dialect that do not support something could raise not implemented like it happens in other parts of sqlalchemy
Reacted by Ramazan Elsunkaev, BrychDaneel, Leon Almeida, F.D.Castel, Dave Hirschfeld, Charles Machalow, yolu-kxs, James Midkiff, Dmitry Dandykin, Andrew Elgert and 4 more
Activity
[-]Unify INSERT ... ON CONFLICT functionality in single implementation[/-][+]Unify INSERT ... ON CONFLICT / UPSERT / MERGE functionality in single implementation[/+][-]Unify INSERT ... ON CONFLICT / UPSERT / MERGE functionality in single implementation[/-][+]Unify INSERT ... ON CONFLICT / UPSERT functionality in single implementation[/+][-]Unify INSERT ... ON CONFLICT / UPSERT functionality in single implementation[/-][+]Unify INSERT ... ON CONFLICT / UPSERT / MERGE functionality in single implementation[/+]zzzeek commentedon Oct 28, 2023
out of scope for 2.1
fdcastel commentedon Jan 8, 2024
I'm also interested in this one. This is a very common scenario, and it's hard to imagine any RDBMS in today's times that doesn't offer anything in this direction.
For reference: When designing the new API, please take a look into the Firebird options to solve this:
UPDATE OR INSERT command
MERGE command
pythonweb2 commentedon Sep 8, 2025
I ran into this today. I'm trying to run my tests in a SQLite database, because it can run in memory, and it is easy to setup/tear down the database. I have a statement like this where I insert where a unique constraint is present, and would like it to work for both postgresql and sqlite.
Amusingly, it seems like the syntax may be the same for both of them, but what is the recommended approach to writing statements that can be used in multiple dialects? Or perhaps the conditional flow for doing such. I searched through the docs but didn't see anything like that.
zzzeek commentedon Sep 8, 2025
if you're targeting postgresql and sqlite, you can probably use the postgresql insert() object and just run it with the sqlite engine and it might just work since the two are implemented as similarly as possible. you of course would have to use the subset of syntaxes that works on both backends.
or you could do a compiles recipe: https://docs.sqlalchemy.org/en/20/core/compiler.html that takes in the PG version of the statement and rewrites it as a SQLite one, if there's some incompatibility to be resolved.
CaselIT commentedon Oct 16, 2026
Just though of this option.
given that 2.1 added https://docs.sqlalchemy.org/en/21/core/compiler.html#sqlalchemy.sql.SyntaxExtension applied to a statement using https://docs.sqlalchemy.org/en/21/core/selectable.html#sqlalchemy.sql.expression.Select.ext and similar, and that the current insert logic are also already built using this extension pattern, I wonder if a pre-database logic could maybe bolt on this api, ideally directly in the 2.1 series as backward compatible, so that an insert could include different db-scoped extension clauses for the on conflict logic.
something like
sqlachemy.insert(...).ext(the_pg_thing, 'pg').ext(the_sqlite_thing, 'sqlite')etc?This would still not be unified, but it's better than nothing I guess.
@zzzeek shame we didn't think about using ext for this from the start.
zzzeek commentedon Oct 16, 2026
this is an example of an issue where I've always felt the thinking, edge case catching, and understanding of exactly what would be most useful, was both not worth it for me and also I didnt have my own experiences needing "cross-database-UPSERT" to really understand what would be useful.
now that we have robots it might be worth starting off by doing something in sqlalchemy.ext here.