On 8 October 2014 21:16, Peter Geoghegan <p...@heroku.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
