title: Using SQLAlchemy's on_conflict_do_update() with unique constraints
body: <p>I'm using SQLAlchemy 1.3.4 and PostgreSQL 11.3.</p>
  
  <p>I have the following (simplified) table definition:</p>
  
  <pre class="lang-py prettyprint-override"><code>class MyModel(Base):
      __tablename__ = 'mymodel'
  
      id = Column(Integer, primary_key=True)
      col1 = Column(Unicode, nullable=False)
      col2 = Column(Unicode, nullable=False)
      col3 = Column(Unicode, nullable=False)
      col4 = Column(Boolean)
  
      created_at = Column(DateTime(timezone=True), nullable=False)
      updated_at = Column(DateTime(timezone=True), nullable=False)
  
      __table_args__ = (
          Index('uq_mymodel_col1_col2_col3_col4',
                col1, col2, col3, col4,
                unique=True, postgresql_where=col4.isnot(None)),
          Index('uq_mymodel_col1_col2_col3',
                col1, col2, col3,
                unique=True, postgresql_where=col4.is_(None)),
      )
  </code></pre>
  
  <p>(I had to create 2 unique index rather than a <code>UniqueConstraint</code> because a <code>UniqueConstraint</code> would allow multiple rows with the same <code>(col1, col2, col3)</code> is <code>col4</code> is null, which I do not want) </p>
  
  <p>I'm trying to do the following query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO mymodel (col1, col2, col3, col4, created_at, updated_at)
  VALUES (%(col1)s, %(col2)s, %(col3)s, %(col4)s, %(created_at)s, %(updated_at)s)
  ON CONFLICT DO UPDATE SET updated_at = %(param_1)s
  RETURNING mymodel.id
  </code></pre>
  
  <p>I can't figure out how to properly use SQLAlchemy's <code>on_conflict_do_update()</code> though. :-/</p>
  
  <p>Here is what I tried:</p>
  
  <pre class="lang-py prettyprint-override"><code>values = {…}
  
  stmt = insert(MyModel.__table__).values(**values)
  stmt = stmt.returning(MyModel.__table__.c.id)
  stmt = stmt.on_conflict_do_update(set_={'updated_at': values['updated_at']})
  result = dbsession.connection().execute(stmt)
  </code></pre>
  
  <p>However SQLAlchemy complains: <code>Either constraint or index_elements, but not both, must be specified unless DO NOTHING</code></p>
  
  <p>I find it very unclear how to use <code>constraint</code> or <code>index_elements</code>.</p>
  
  <p>I tried a few things, to no avail. For example:</p>
  
  <pre class="lang-py prettyprint-override"><code>values = {…}
  
  stmt = insert(MyModel.__table__).values(**values)
  stmt = stmt.returning(MyModel.__table__.c.id)
  stmt = stmt.on_conflict_do_update(constraint='uq_mymodel_col1_col2_col3_col4'
                                    set_={'updated_at': values['updated_at']})
  result = dbsession.connection().execute(stmt)
  </code></pre>
  
  <p>But then this doesn't work either: <code>constraint "uq_mymodel_col1_col2_col3_col4" for table "mymodel" does not exist</code>. But it does exist. (I even copy-pasted from pgsql to make sure I hadn't made a typo)</p>
  
  <p>In any case, I have two unique constraints which can raise a conflict, but <code>on_conflict_do_update()</code> seems to only take one. So I also tried specifying both like this:</p>
  
  <pre class="lang-py prettyprint-override"><code>values = {…}
  
  stmt = insert(MyModel.__table__).values(**values)
  stmt = stmt.returning(MyModel.__table__.c.id)
  stmt = stmt.on_conflict_do_update(constraint='uq_mymodel_col1_col2_col3_col4'
                                    set_={'updated_at': values['updated_at']})
  stmt = stmt.on_conflict_do_update(constraint='uq_mymodel_col1_col2_col3'
                                    set_={'updated_at': values['updated_at']})
  result = dbsession.connection().execute(stmt)
  </code></pre>
  
  <p>But I get the same error, that the <code>uq_mymodel_col1_col2_col3_col4</code> does not exist.</p>
  
  <p>At this point I just can't figure out how to do the above query, and would really appreciate some help.</p>