--- Tom Allison <tallison@tacocat.net> wrote: > I seemed to remember being able to do this but I > can't find the docs. > > Can I run a sql query to insert new or update > existing rows in one query? > > Otherwise I have to run a select query to see if > it's there and then > another one to update/insert. This is what you have to do. This question comes up a lot on the lists. You can read endless discussions about it if you want to search the archives. The issue is concurrency, i.e. multiple users accessing the data at the same time, and perhaps two of them wanting to do the same update-else-insert combination at the same time. Then you have the so called "race condition", i.e. user1 does a select, finds the record does not exist, attempts to insert; in between those, user2 inserts the row. So, you now either have duplicate data (bad), or user1's insert fails because of a unique constraint (also bad, because the operation has failed). The only way to guarantee against this is to lock the table for the duration of the exercise, which prevents any concurrent access at all. This may be acceptable if you have few users, or a low insert/update load, but may be a performance killer otherwise. Every now and then someone pops up on the list(s) claiming to have found some new miracle method for getting around these limitations, but no such has yet been proven. > > What I'm trying to do is create a counter for each > key, insert a value > of 1 or increment the value by 1 and then set > another specific row > (where key = $key) to always increment by 1. > > And the more I type, the more this sounds like the > answer is going to be > part function, part trigger.... Maybe I should post > to 'novice' for a > while! ;) > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list cleanly __________________________________ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
