"Florian G. Pflug" <[EMAIL PROTECTED]> writes: >>> INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; >>> This allow to make an insert, and if the key is already there to modify the >>> value depending on the current one. > > May this could be generalized to a generic "<stmt> on <error> do <stmt>"? > You could then write > "update table set c=c+1 on not_found do insert into table (a,b,c) values > (1,2,3)" > > Just an idea I just had...
We have such a thing, subtransactions. The reason UPSERT or ON DUPLICATE is interesting is because it provides a way to do it atomically. That is, you keep the locks acquired from the duplicate key check and if it fails you update the same records you just found violating the duplicate key. If the user tries to do the same thing he has to repeat the search after the duplicate key check has released the locks so it's possible they've been deleted or updated since. So the user has to loop in case the update fails to find any records and he has to start over trying to insert. The same problem plagues you if you do it the other way around too. The tricky part is avoiding race conditions. The way the unique index code avoids having someone else come along and insert at the same time is by holding a lock on an index page. I'm not sure if you can keep that lock while you go lock the tuples for the update. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org