On 25 September 2014 15:35, Robert Haas <robertmh...@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. Let's keep this new feature as simple as possible. ORMs everywhere need to be encouraged to implement this and they won't do it unless it is bone simple to use. -- 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
