- changed title to Support Postgres 9.5 "ON CONFLICT" (upserts)
Support Postgres 9.5 "ON CONFLICT" (upserts)
Probably a bit early since 9.5 isn't out yet, but once it's out this would be pretty useful.
This could be a table arg, e.g. postgres_onconflict='nothing|update'
(or maybe even in a less db-specific way if other databases also support this feature). When set it would automatically add it to INSERTs involving that table.
Comments (13)
-
reporter -
reporter - edited description
-
repo owner - changed status to duplicate
Duplicate of #960.
-
repo owner yep. its a crapshow, everyone does it totally differently yet accomplishes almost the same thing.
-
I am not sure if a table arg would make much sense at all...
-
Here is a working version on my fork, with tests included, for the common uses of INSERT ... ON CONFLICT. No documentation yet. I won't do a pull request until I get some feedback from all of you...
https://bitbucket.org/robin900/sqlalchemy/branch/ticket_3529#diff
Usage is:
table.insert(postgresql_on_conflict='update').values(**thevalues) table.insert(postgresql_on_conflict='nothing').values(**thevalues) # can use OnConflictBase objects themselves, a work in progress to cover different usage (conflict targets, custom ON UPDATE SET ...) from sqlalchemy.dialects.postgresql.on_conflict import DoNothing, DoUpdate table.insert(postgresql_on_conflict=DoNothing([table.c.col1, table.c.col2])).values(**thevalues) table.insert(postgresql_on_conflict=DoUpdate([table.c.col1, table.c.col2]).with_excluded(table.c.col3, table.c.col4)).values(**thevalues)
-
Any chance of @robin900's patch making it into a production release of SQLAlchemy? I'd like to be able to use this functionality soonish and hopefully in a form that won't break compatibility with future SQLAlchemy releases.
-
repo owner - changed status to open
@jace gee thanks for the encouragement! :) not sure if you noticed I spent all day thursday working on the gerrit
-
repo owner - changed status to resolved
Add ON CONFLICT support for Postgresql
Fixes:
#3529Co-authored-by: Mike Bayer mike_mp@zzzcomputing.com Change-Id: Ie3bf6ad70d9be9f0e44938830e922db03573991a Pull-request: https://github.com/zzzeek/sqlalchemy/pull/258→ <<cset 4e9ab7a72f0a>>
-
Yay! (I hadn't realised the action had moved elsewhere.)
-
Hi, Guys
What about ORM support. I can't find in commit any usecase with ORM
is it possible to do something like this:
user = User(name='foo', on_coflict_do_nothing=True) db.session.add(user) db.session.commit()
-
repo owner @erebus1 the ORM's semantics would make such a case impossible. the ORM always knows, given a row, if that row exists already or not. In your example, you are adding a transient User object, so that means, "this row does not exist". It would do an INSERT.
Of course you can work an ORM object into an ON CONFLICT DO NOTHING but this would not be via session.add(), it would be an external method which you can of course roll easily given this User object.
-
repo owner to be clear this would at best be a use case for session.merge(), but it would emit the row immediately. But it would be better as a feature that is more tailored to what this statement can do, e.g. it can also run against lots of rows at once.
- Log in to comment