Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 8 October 2014 21:16, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

>> My opinion is that syntax for this should be similar to MERGE in the
>> *body* of the command, rather than some completely different syntax.
>> e.g.
>>
>>> WHEN NOT MATCHED THEN
>>> INSERT
>>> WHEN MATCHED THEN
>>> UPDATE
>>
>> I'm happy that we put that to a vote on what the syntax should be, as
>> long as we bear in mind that we will one day have MERGE as well.
>
> While I am also happy with taking a vote, if we do so I vote against
> even this much less MERGE-like syntax. It's verbose, and makes much
> less sense when the mechanism is driven by would-be duplicate key
> violations rather than an outer join.

It wouldn't be driven by an outer join, not sure where that comes from.

MERGE is verbose, I agree. I don't always like the SQL Standard, I
just think we should follow it as much as possible. You can't change
the fact that MERGE exists, so I don't see a reason to have two
variants of syntax that do roughly the same thing.

MERGE syntax would allow many things, such as this...
WHEN NOT MATCHED AND x > 7 THEN
INSERT
WHEN NOT MATCHED THEN
INSERT
WHEN MATCHED AND y = 5 THEN
DO NOTHING
WHEN MATCHED THEN
UPDATE

etc

> I also like that when you UPSERT
> with the proposed ON CONFLICT UPDATE syntax, you get all the
> flexibility of an INSERT - you can use data-modifying CTEs, and nest
> the statement in a data-modifying CTE, and "INSERT ... SELECT... ON
> CONFLICT UPDATE ..." . And to be honest, it's much simpler to
> implement this whole feature as an adjunct to how INSERT statements
> are currently processed (during parse analysis, planning and
> execution); I don't want to make the syntax work against that.

I spoke to someone today that preferred a new command keyword, like
UPSERT, because the semantics of triggers are weird. Having a before
insert trigger fire when there is no insert is quite strange. Properly
documenting that on hackers would help; has the comments made on the
Django list been replayed here in some form?

I'm very scared by your comments about data modifying CTEs etc.. You
have no definition of how they will work, not tests of that. That part
isn't looking like a benefit as things currently stand.

I'm still waiting for some more docs to describe your intentions so
they can be reviewed.

Also, it would be useful to hear that your're going to do something
about the references to rows using conflicting(), since nobody has
agreed with you there. Or hopefully even that you've listened and
implemented something differently already. (We need that, whatever we
do with other elements of syntax).

Overall, I'm not seeing too many comments that indicate you are
accepting review comments and acting upon them. If you want acceptance
from others, you need to begin with some yourself.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services