Support Postgres 9.5 "ON CONFLICT" (upserts)

Issue #3529 resolved
Adrian
created an issue

Probably a bit early since 9.5 isn't out yet, but once it's out this would be pretty useful.

https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29

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 (15)

  1. Robin T

    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)
    
  2. Kiran Kiran

    Any chance of @Robin T'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.

  3. erebus1

    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()
    
  4. Michael Bayer repo owner

    @Igor Kasianov 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.

  5. Michael Bayer 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.

  6. Michael Bayer repo owner

    on the ML I got into how modifying the UOW to do an insert-on-conflict style query could be possible but the issue is not emitting the SQL, it's the session object lifecycle that would be broken here. I can't think of all the things that would have to be figured out off the top of my head, but these include: do we emit the before_insert / after_insert / before_update / after_update events? or make new events (probably the latter), what happens when the user "add_for_merge()"'s multiple objects with the same primary key, but they have not been flushed yet, what do we do then ? I've got "a1, a2, a3" that all have the same PK, all pending. flush happens, they're all the same row. Which one is in the identity map ? things like this. it took years to get this lifecycle /persistence model right and it did not take "insert-on-conflict" into account at all.

  7. Log in to comment