<p dir="auto">Edit: <del>Note: This suggestion is only a change meant for the Postgres dialect!</del></p>
<p dir="auto">Hi there,</p>
<p dir="auto">This is admittedly a nice-to-have for those (like me) who didn't know that an <code class="notranslate">insert</code> statement in Postgres could not have more than one <code class="notranslate">on conflict</code> clause.  If I were to try and perform an insert such as:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="insert into table (col1, col2) values (val1, val2)
on conflict (col1) do update set col1 = 'something'
on conflict (col2) do update set col2 = 'something_else';"><pre class="notranslate"><code class="notranslate">insert into table (col1, col2) values (val1, val2)
on conflict (col1) do update set col1 = 'something'
on conflict (col2) do update set col2 = 'something_else';
</code></pre></div>
<p dir="auto">This would fail with a syntax error in Postgres, even though the intent of the query seems kind of sound (do one kind of update if some constraint is violated, and do another kind of update if some other constraint is violated)</p>
<p dir="auto">With SQLAlchemy, when executing a query such as:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query = (
    insert(ModelClass.__table__)
    .values(...)
    .on_conflict_do_update(...some_args)
    .on_conflict_do_update(...some_args2)
)"><pre class="notranslate"><code class="notranslate">query = (
    insert(ModelClass.__table__)
    .values(...)
    .on_conflict_do_update(...some_args)
    .on_conflict_do_update(...some_args2)
)
</code></pre></div>
<p dir="auto">The query builds valid SQL, but what is sent to the database is a statement of the form</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="insert into model_table (...) values (...)
on conflict (...) do update set something = 'some_args2'; -- only the second on_conflict statement is present"><pre class="notranslate"><code class="notranslate">insert into model_table (...) values (...)
on conflict (...) do update set something = 'some_args2'; -- only the second on_conflict statement is present
</code></pre></div>
<p dir="auto">I personally think it's a bit unintuitive to be able to build what you might think is a query with many on conflict statements without error, but silently it is executing the wrong SQL and is choosing to keep only one <code class="notranslate">on conflict</code> clause.</p>
<p dir="auto">So, my request is, when building a query such as:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query = (
    insert(ModelClass.__table__)
    .values(...)
    .on_conflict_do_update(...some_args)
    .on_conflict_do_update(...some_args2)
)"><pre class="notranslate"><code class="notranslate">query = (
    insert(ModelClass.__table__)
    .values(...)
    .on_conflict_do_update(...some_args)
    .on_conflict_do_update(...some_args2)
)
</code></pre></div>
<p dir="auto">I would really like to see an InvalidRequestError raised (much like the example in <a href="https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/dml.py#L419">https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/sql/dml.py#L419</a>, where calling values() with some kwargs when the query is already inserting from a select is deemed illegal) that states something like: "This construct already has an on_conflict behavior set"</p>
<p dir="auto">More than happy to make a PR for this! I'd imagine it's a pretty simple change since it only affects the postgres dialect/DML, but I'd love to hear your thoughts on whether the change is merited (and also what the error message should be). Also, if the change is merited, perhaps <code class="notranslate">on_conflict_do_nothing</code> should also raise when it is trying to overwrite an on_conflict behavior that's already set.</p>
<p dir="auto">Thanks!</p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">using the "criterion" in a Query, invalidate objects, or just a subset of their attributes, within the session meeting this criterion, without issuing SQL for rudimentary criterion.  for more complex criterion like EXISTS, may need to issue SQL.  will need to add ClauseElement evaluation routines to accomplish this.</p>
<p dir="auto">while we really don't like to duplicate the behavior of a SQL database in Python, this is one case that is quite important, as it would allow mass UPDATE and DELETE statements to be reflected in the current session state without the need to clear or invalidate the entire session.</p>