title: How to do a proper upsert using sqlalchemy on postgresql?
body: <p>I would like to do an upsert using the &quot;new&quot; functionality added by postgresql 9.5, using sqlalchemy core. While it is implemented, I'm pretty confused by the syntax, which I can't adapt to my needs.
  Here is a sample code of what I would like to be able to do :</p>
  <pre><code>from sqlalchemy.ext.declarative import declarative_base
  
  Base = declarative_base()
  class User(Base):
      __tablename__ = 'test'
      a_id = Column('id',Integer, primary_key=True)
      a = Column(&quot;a&quot;,Integer)
  
  engine = create_engine('postgres://name:password@localhost/test')
  User().metadata.create_all(engine)
  meta = MetaData(engine)
  meta.reflect()
  table = Table('test', meta, autoload=True)
  conn = engine.connect()
  
  from sqlalchemy.dialects.postgresql import insert as psql_insert
  stmt = psql_insert(table).values({
      table.c['id']: bindparam('id'),
      table.c['a']: bindparam('a'),
  })
  stmt = stmt.on_conflict_do_update(
      index_elements=[table.c['id']],
      set_={'a': bindparam('a')},
  )
  list_of_dictionary = [{'id':1, 'a':1, }, {'id':2, 'a':2,}]
  conn.execute(stmt, list_of_dictionary)
  </code></pre>
  <p>I basically want to insert a bulk of rows, and if one id is already taken, I want to update it with the value I initially wanted to insert.
  However sqlalchemy throw me this error :</p>
  <pre><code>CompileError: bindparam() name 'a' is reserved for automatic usage in the VALUES or SET clause of this insert/update statement.   Please use a name other than column name when using bindparam() with insert() or update() (for example, 'b_a').
  </code></pre>
  <p>While it is a known issue (see <a href="https://groups.google.com/forum/#!topic/sqlalchemy/VwiUlF1cz_o" rel="noreferrer">https://groups.google.com/forum/#!topic/sqlalchemy/VwiUlF1cz_o</a>), I didn't found any proper answer that does not require to modify either the keys of list_of_dictionary or the name of your columns.</p>
  <p>I want to know if there is a way of constructing stmt in a way to have a consistent behavior that does not depends on whether the keys of the variable list_of_dictionary are the name of the columns of the inserted table (my code works without error in those cases).</p>