On Wed, 2014-10-08 at 01:10 -0700, Peter Geoghegan wrote: > On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen > <anssi.kaariai...@thl.fi> wrote: > > The MySQL documentation says that "you should try to avoid using an ON > > DUPLICATE KEY UPDATE clause on tables with multiple unique indexes". > > The proposed feature's documentation has the same suggestion. Still, > > the feature defaults to this behavior. Why is the default something the > > documentation says you shouldn't do?
> As we all know, naming a unique index in DML is ugly, and has poor > support in ORMs. It seems likely that we're better off making it > optional - it hasn't been much of a problem with the existing subxact > looping pattern. The subxact approach is a bit different than the proposed UPSERT command. It loops: try: INSERT INTO author VALUE('Jack', 't...@example.com', 34) except UniqueConstraintViolation: UPDATE author SET ... WHERE name = 'Jack' while the UPSERT command does something like: try: INSERT INTO author VALUE('Jack', 't...@example.com', 34) except UniqueConstaintViolation: UPDATE author SET ... WHERE name = 'Jack' OR email = 't...@example.com' LIMIT 1; - Anssi -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers