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

  1. Mike Bayer repo owner

    yep. its a crapshow, everyone does it totally differently yet accomplishes almost the same thing.

  2. Robin Thomas

    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)
    
  3. Kiran Jonnalagadda

    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.

  4. Mike Bayer 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

  5. 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()
    
  6. Mike Bayer 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.

  7. Mike 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.

  8. Log in to comment