On Thu, Sep 25, 2014 at 11:21 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 25 September 2014 15:35, Robert Haas <robertmhaas@gmail.com> wrote:
>>> The only problem I see is if the newly inserted row matches one row on
>>> one unique value and a different row on a different unique index.
>>> Turning the INSERT into an UPDATE will still fail on one or other, no
>>> matter which index you pick. If there is one row for ALL unique
>>> indexes then it is irrelevant which index you pick. So either way, I
>>> cannot see a reason to specify an index.
>>
>> Failure could be the right thing in some cases. For example, imagine
>> that a user has a table containing names, email addresses, and (with
>> apologies for the American-ism, but I don't know what would be
>> comparable elsewhere) social security numbers. The user has unique
>> indexes on both email addresses and SSNs. If a new record arrives for
>> the same email address, they want to replace the existing record; but
>> a new record arrives with the same SSN, they want the transaction to
>> fail. Otherwise, a newly-arrived record might overwrite the email
>> address of an existing record, which they never want to do, because
>> they view email address as the primary key.
>
> I agree with your example, but not your conclusion.
>
> If a new record arrives with a new email address that matches an
> existing record it will fail. There is a case that would be allowed,
> which would be a record that creates an entirely new email address. So
> you do have a point to argue from.
>
> However, IMV enforcing such a restriction should be done with an After
> trigger, which is already possible, not by complicating a DML
> statement with information it shouldn't need to know, or that might
> change in the future.
I've never been a fan of putting the index name in there. I agree
that's stuff that a DML statement shouldn't need to know about. What
I've advocated for in the past is specifying the list of columns that
should be used to determine whether to insert or update. If you have
a match on those columns, update the row; else insert. Any other
unique indexes stand or fall as may be.
I still think that idea has merit.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company